SUM doesnt work
in [Programming SQL]
|
From: Federico Moschini on 22 Jul 2008 11:00 I have tried this sql but rows where the "VD" is the same is not summed. What is the error ? SELECT (VD.perc * RIGHEDOCUMENTI.TOTNETTORIGA) / 100 AS VALUETOT, VD.VD, VD.perc, ANAGRAFICAARTICOLI.CODICE, RIGHEDOCUMENTI.NUMERODOC, sum ((VD.perc * RIGHEDOCUMENTI.TOTNETTORIGA) / 100) as pinco,RIGHEDOCUMENTI.CODART, ANAGRAFICAARTICOLI.NOMENCLCOMBINATA1, RIGHEDOCUMENTI.TOTNETTORIGA, RIGHEDOCUMENTI.TIPODOC FROM RIGHEDOCUMENTI CROSS JOIN VD CROSS JOIN ANAGRAFICAARTICOLI GROUP BY VD.VD, VD.perc, ANAGRAFICAARTICOLI.CODICE, RIGHEDOCUMENTI.NUMERODOC, RIGHEDOCUMENTI.CODART, ANAGRAFICAARTICOLI.NOMENCLCOMBINATA1, RIGHEDOCUMENTI.TOTNETTORIGA, RIGHEDOCUMENTI.TIPODOC HAVING (RIGHEDOCUMENTI.NUMERODOC = @NUMERODOC) AND (VD.VD = ANAGRAFICAARTICOLI.NOMENCLCOMBINATA1) AND (ANAGRAFICAARTICOLI.CODICE = RIGHEDOCUMENTI.CODART) AND (RIGHEDOCUMENTI.TIPODOC = 'ORF') AND (ANAGRAFICAARTICOLI.NOMENCLCOMBINATA1 = ANAGRAFICAARTICOLI.NOMENCLCOMBINATA1)
From: Roy Harvey (SQL Server MVP) on 22 Jul 2008 11:24 One possibility is that the data in "VD" is actually different in some way that is not obvious. For example there could be a non-printable character. But another possibility is a misunderstanding in how GROUP BY works. Why not post the results, or an excerpt from the results, that shows the problem? As an aside, I would have placed the HAVING clause tests in a WHERE clause instead. Roy Harvey Beacon Falls, CT On Tue, 22 Jul 2008 17:00:29 +0200, "Federico Moschini" <federicom(a)metroint.it> wrote: >I have tried this sql but rows where the "VD" is the same is not summed. > >What is the error ? > >SELECT (VD.perc * RIGHEDOCUMENTI.TOTNETTORIGA) / 100 AS VALUETOT, VD.VD, >VD.perc, ANAGRAFICAARTICOLI.CODICE, RIGHEDOCUMENTI.NUMERODOC, sum ((VD.perc >* RIGHEDOCUMENTI.TOTNETTORIGA) / 100) as pinco,RIGHEDOCUMENTI.CODART, >ANAGRAFICAARTICOLI.NOMENCLCOMBINATA1, RIGHEDOCUMENTI.TOTNETTORIGA, >RIGHEDOCUMENTI.TIPODOC FROM RIGHEDOCUMENTI CROSS JOIN VD CROSS JOIN >ANAGRAFICAARTICOLI GROUP BY VD.VD, VD.perc, ANAGRAFICAARTICOLI.CODICE, >RIGHEDOCUMENTI.NUMERODOC, RIGHEDOCUMENTI.CODART, >ANAGRAFICAARTICOLI.NOMENCLCOMBINATA1, RIGHEDOCUMENTI.TOTNETTORIGA, >RIGHEDOCUMENTI.TIPODOC HAVING (RIGHEDOCUMENTI.NUMERODOC = @NUMERODOC) AND >(VD.VD = ANAGRAFICAARTICOLI.NOMENCLCOMBINATA1) AND >(ANAGRAFICAARTICOLI.CODICE = RIGHEDOCUMENTI.CODART) AND >(RIGHEDOCUMENTI.TIPODOC = 'ORF') AND (ANAGRAFICAARTICOLI.NOMENCLCOMBINATA1 = >ANAGRAFICAARTICOLI.NOMENCLCOMBINATA1) >
From: Federico Moschini on 22 Jul 2008 11:49 This is what has been displayed. Considering that VD is the same for the 2 rows, I need only 1 row with total value VALUETOT VD perc CODICE NUMERODOC pinco CODART NOMENCLCOMBINATA1 TOTNETTORIGA TIPODOC 882.5760000 xxxxxxxx 4.5000 T2928CW 800104 882.5760000 T2928CW xxxxxxxx 19612.8000 ORF 633.2040000 xxxxxxxx 4.5000 T2935CW 800104 633.2040000 T2935CW xxxxxxxx 14071.2000 ORF
From: Roy Harvey (SQL Server MVP) on 22 Jul 2008 12:11 On Tue, 22 Jul 2008 17:49:26 +0200, "Federico Moschini" <federicom(a)metroint.it> wrote: >This is what has been displayed. Considering that VD is the same for the 2 >rows, I need only 1 row with total value > > VALUETOT VD perc CODICE NUMERODOC pinco CODART NOMENCLCOMBINATA1 >TOTNETTORIGA TIPODOC > 882.5760000 xxxxxxxx 4.5000 T2928CW 800104 882.5760000 T2928CW >xxxxxxxx 19612.8000 ORF > 633.2040000 xxxxxxxx 4.5000 T2935CW 800104 633.2040000 T2935CW >xxxxxxxx 14071.2000 ORF It appears that the fourth column, CODICE, has two different values, T2928CW and T2935CW. Since the query includes C.CODICE as a column in the GROUP BY these rows will not be consolidated. The way GROUP BY works is that the combination of all columns or expressions in the GROUP BY list ends up unique. That does not make any single column unique, only the combination of all of them. Note that every column in the SELECT list must either also appear in the GROUP BY list or be an aggregate. Roy Harvey Beacon Falls, CT
From: Aaron Bertrand [SQL Server MVP] on 22 Jul 2008 12:30
And which value for CODICE / CODART would you want to display in this one row, T2928CW or T2935CW? And if you can pick one, can you explain why? On 7/22/08 11:49 AM, in article #SsqeKB7IHA.3672(a)TK2MSFTNGP04.phx.gbl, "Federico Moschini" <federicom(a)metroint.it> wrote: > This is what has been displayed. Considering that VD is the same for the 2 > rows, I need only 1 row with total value > > VALUETOT VD perc CODICE NUMERODOC pinco CODART NOMENCLCOMBINATA1 > TOTNETTORIGA TIPODOC > 882.5760000 xxxxxxxx 4.5000 T2928CW 800104 882.5760000 T2928CW > xxxxxxxx 19612.8000 ORF > 633.2040000 xxxxxxxx 4.5000 T2935CW 800104 633.2040000 T2935CW > xxxxxxxx 14071.2000 ORF > > |