January 10, 2005 at 4:54 am
Hello community
Happy 2005 for all of you
I need to exclude on the GROUP BY clause some fields of my SELECT statment because in this example for the same 'Nome cliente' (Client Name ) this select return to me 2 lines ,one with (NDOC =1 and 4 - Invoices) and an other one with (NDOC=8 and 3 -Credit Notes), also i have the name of this documents in my SELECT that is NMDOC and they are different regarding the NDOC number.
I´m attach my SELECT Statment and i hope someone could give me a method for excluding Select fields in the Group by Clause.
My SELECT statment:
select fi.ndoc,fi.nmdoc,fi.ref as 'codigo',ft.no,ft.nome as 'Nome cliente',fi.design,sum(fi.qtt) as 'Qt' ,(fi.epv-(fi.epv*(ft.fin/100))) as pv,(case when (fi.ndoc=1 or fi.ndoc=3) then fi.ecusto else case when (fi.ndoc=8 or fi.ndoc=4) then fi.ecusto else 0 end end) as 'CM',
(select case when sum(fi.Etiliquido)=0 then 0 else sum(fi.etiliquido)-(sum(fi.etiliquido)*(ft.fin/100)) end) as 'Valor',
(case when (fi.ndoc=1 or fi.ndoc=3) then (fi.ecusto*sum(fi.qtt)) else case when (fi.ndoc=8 or fi.ndoc=4) then (fi.ecusto*sum(fi.qtt)*-1) else 0 end end) as 'Valor Custo',
ltrim(str(desconto))+'+'+ ltrim(str(desc2)) as 'Desc',
(case when fi.ndoc=1 then (1-((fi.ecusto*sum(fi.qtt))/(case when sum(fi.Etiliquido)=0 then 1 else sum(fi.etiliquido)-(sum(fi.etiliquido)*(ft.fin/100) )end)))*100 else 0 end) as Margem,
'Comissão' =(sum(fi.etiliquido)-sum(fi.etiliquido)*(ft.fin/100))*
/*GERAL - sem tipo cliente e sem vendedor na ficha do cliente*/
((CASE WHEN PN.VENDEDOR IN(SELECT ECOM.VENDEDOR FROM ECOM WHERE ECOM.VENDEDOR=10) and pn.tipo like ' ' THEN
(SELECT ECOM.COM1 from ecom WHERE ecom.tipocl=0 and ecom.PORVEND=0 AND ECOM.VENDEDOR=0 AND ECOM.QUALTIPO = ' ' AND PN.ECOMISSAO=ecom.ecom)
ELSE
/*Especfica de vendedor*/
CASE WHEN PN.VENDEDOR IN(SELECT ECOM.VENDEDOR FROM ECOM WHERE ECOM.VENDEDOR=10) THEN
(SELECT ECOM.COM1 from ecom WHERE ecom.PORVEND=1 AND ECOM.VENDEDOR=10 )
else
/*Tipo de Cliente com vendedor na ficha */
CASE WHEN PN.VENDEDOR not IN(SELECT ECOM.VENDEDOR FROM ECOM WHERE ECOM.VENDEDOR=10) and pn.tipo not like ' ' then (select ecom.com1 from ecom where ecom.tipocl=1 and ecom.porvend=0 and ecom.qualtipo=pn.tipo)
else
/*Tipo de Cliente - sem vendedor na ficha*/
CASE WHEN PN.VENDEDOR IN(SELECT ECOM.VENDEDOR FROM ECOM WHERE ECOM.VENDEDOR=10) and pn.tipo not like ' ' then (select ecom.com1 from ecom where ecom.tipocl=1 and ecom.vendedor=0 and ecom.porvend=0 and ecom.qualtipo=pn.tipo) end END end END)/100)
from fi,ft,pn where
fi.ftstamp=ft.ftstamp and fi.fistamp=pn.pnstamp and ft.no in(#1#) and fi.ref <> ' ' and (FI.STNS=0 or FI.STNS=1) and ft.fdata >=#2# and ft.fdata<=#3# and (fi.ndoc=1 or fi.ndoc=8 or fi.ndoc=3 or fi.ndoc=4)
group by fi.ref,fi.epv,fi.ecusto,ltrim(str(desconto))+'+'+ ltrim(str(desc2)),ft.nome,fi.ndoc,fi.nmdoc,ft.no,fi.design,ft.efinv,ft.fin,pn.ecomissao,pn.vendedor,pn.tipo
order by fi.ndoc,fi.ref asc
Best regards
Luis Santos
January 10, 2005 at 8:40 am
Hi there.
Well, I can't begin to try understanding what is going on, or the purpose of it all, however it seems like there's a lot of calculations, searched cases and stuff in there... Very briefly, I don't think that you can change the group by in any easy way... (At least not without further knowledge of the underlying tables and what the query should return)
A common way, though, of 'reducing' columns, is to nest queries as virtual tables. If the query above is to be seen as an 'intermediate table', or 'temp table/result', you could use it that way. Something along these lines:
SELECT
January 10, 2005 at 10:05 am
While I don't like temp tables, it might help here. Load some of the data into a temp table and get some calculations done there.
You have to have all the non-aggregated columns in a select in the group by. If you need less, you'd need to rework the statement by doing the group by in a tmep table or subselect and then adding in more fields in an outer select that joins to the inner select.
January 10, 2005 at 2:48 pm
For others bennifit and the fact it helps me understand better could you post an example of the data you are currently getting and an example of what you want to get along with the reason why? I agree with Steve a temp table might help but you might also find a subquery will sometimes do the trick.
January 10, 2005 at 9:25 pm
Hello Luis Santos,
I agree with Antares686, a sample output -- particularly of the two lines that you want consolidated into 1 -- would make this easier to solve. However, I'll hazard a guess:
You say above: "also I have the name of this documents in my SELECT -- that is NMDOC and they are different regarding the NDOC number."
If you mean the column NMDOC contains something like 'INV' when NDOC=1, and it contains something like 'CRD' when NDOC = 8, then column NMDOC is the culprit. In fact, on closer look I see that column NDOC is also in the GROUP BY phrase. I would remove NDOC and NMDOC from the GROUP BY phrase (and I see that NDOC is also in the ORDER BY, so remove that too).
Perhaps that's your solution.
Bob Monahon
January 11, 2005 at 7:14 am
Hello
Replie for Antares686 as requested :
| Ndoc | Nmdoc | Codigo | Qt | Pv | Valor | Valor_custo | Desc | Margem | Comissão |
| 1 | Factura | 20180ANB-1 | 1256 | 8,35 | 10487,6 | 7787,2 | 0+0 | 25,7486 | |
| 1 | Factura | 20180ANB-2 | 2400 | 8,35 | 20040 | 14880 | 0+0 | 25,7486 | |
| 1 | Factura | 22520ANB-1 | 1500 | 8,25 | 12375 | 8572,5495 | 0+0 | 30,7269 | |
| 1 | Factura | 22520ANB-2 | 1500 | 8,25 | 12375 | 8580 | 0+0 | 30,6667 | |
| 1 | Factura | 24020ANB-1 | 2500 | 5 | 12500 | 8581,8525 | 0+0 | 31,3452 | |
| 1 | Factura | 24020ANB-1 | 3100 | 5,5 | 17050 | 10641,4971 | 0+0 | 37,5866 | |
| 1 | Factura | 24040ANB-1 | 1300 | 8 | 10400 | 6290,6701 | 0+0 | 39,5128 | |
| 1 | Factura | 24040ANB-2 | 1200 | 8 | 9600 | 6120 | 0+0 | 36,25 | |
| 1 | Factura | 24040ANB-3 | 860 | 8 | 6880 | 4162,4 | 0+0 | 39,5 | |
| 1 | Factura | 24040ANB-4 | 1500 | 8 | 12000 | 7650 | 0+0 | 36,25 | |
| 1 | Factura | 24520ANB01-04 | 3100 | 6 | 18600 | 11791,0701 | 0+0 | 36,6072 | |
| 1 | Factura | 27640ANB-1 | 420 | 6 | 2520 | 1377,6 | 0+0 | 45,3334 | |
| 1 | Factura | 27640ANB-2 | 1290 | 6 | 7740 | 4231,2 | 0+0 | 45,3334 | |
| 1 | Factura | 27670ANB-1 | 3200 | 6 | 19200 | 9123,9296 | 0+0 | 52,4796 | |
| 1 | Factura | 27670ANB-2 | 2200 | 6 | 13200 | 7026,5514 | 0+0 | 46,7686 | |
| 1 | Factura | 27670ANB-2 | 2400 | 6 | 14400 | 7665,3288 | 0+0 | 46,7686 | |
| 1 | Factura | 27730ANB-1 | 1080 | 5,4 | 5832 | 3186 | 0+0 | 45,3704 | |
| 1 | Factura | 27730ANB-2 | 820 | 5,4 | 4428 | 2419 | 0+0 | 45,3704 | |
| 1 | Factura | PRV108-1 | 3510 | 7 | 24570 | 18145,31004 | 0+0 | 26,1486 | |
| 1 | Factura | PRV108-2 | 4800 | 7,9 | 37920 | 27888 | 0+0 | 26,4557 | |
| 1 | Factura | PRV108-3 | 7000 | 7,9 | 55300 | 41006,882 | 0+0 | 25,8466 | |
| 4 | Nota de Crédito | NSINDEMNIZ | 1 | 12000 | -12000 | -0,65287 | 0+0 | 0 | -480 |
| 4 | Nota de Crédito | NSRAPPEL | 1 | 41217,49 | -41217,49 | 0 | 0+0 | 0 | -1648,6996 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||
| 29 | 46938 | 53362,79 | 274200,11 | 217126,3883 | 775,8179 | -2128,6996 |
that is the result , but i need the result like this :
for this Client i need only one line with the SUM of "Valor" ; "Valor_custo";"Comissões"and "Margem" , where "Valor" as Sails;"Valor_custo" as Cost; "comissões" as comissions and "margem" as benefits.
I hope you understand better what i want to do.
Best regards
Luis Santos
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply