Wrong Results in GROUP BY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Wrong Results in GROUP BY

Author

Topic

nvoyatzopoulos
Starting Member

7 Posts

Posted - 2013-03-11 : 04:58:43

Select CMPIMGSIZESCATDT.CISCODE,
CMPIMGSIZES.DESCR,
CMPIMGGENCAT.DESCR,
CMPIMGSIZESCAT.DESCR,
Sum(Case CMPIMGSIZESDT.CRDB When 0 Then A.XRE * CMPIMGSIZESDT.MSIGN
When 1 Then A.PIS * CMPIMGSIZESDT.MSIGN
When 2 Then (A.XRE - A.PIS) * CMPIMGSIZESDT.MSIGN When 3 Then (Case
When (A.XRE - A.PIS) > 0 Then Abs(A.XRE - A.PIS) * CMPIMGSIZESDT.MSIGN
Else 0 End) When 4 Then (Case
When (A.XRE - A.PIS) < 0 Then Abs(A.XRE - A.PIS) * CMPIMGSIZESDT.MSIGN
Else 0 End) End) AMT
From CMPIMGSIZES Inner Join
CMPIMGSIZESDT On CMPIMGSIZES.CISCODE = CMPIMGSIZESDT.CISCODE And
CMPIMGSIZES.CMPCODE = CMPIMGSIZESDT.CMPCODE Inner Join
CMPIMGSIZESCATDT On CMPIMGSIZESDT.CISCODE = CMPIMGSIZESCATDT.CISCODE
And CMPIMGSIZESDT.CMPCODE = CMPIMGSIZESCATDT.CMPCODE Inner Join
CMPIMGSIZESCAT On CMPIMGSIZESCATDT.CMPCODE = CMPIMGSIZESCAT.CMPCODE And
CMPIMGSIZESCATDT.CISCCODE = CMPIMGSIZESCAT.CISCCODE Inner Join
CMPIMGGENCATDT On CMPIMGSIZESCATDT.CISCCODE = CMPIMGGENCATDT.CISCCODE And
CMPIMGSIZESCATDT.CMPCODE = CMPIMGGENCATDT.CMPCODE Inner Join
CMPIMGGENCAT On CMPIMGGENCATDT.CISGCODE = CMPIMGGENCAT.CISGCODE And
CMPIMGGENCAT.CMPCODE = CMPIMGGENCATDT.CMPCODE Inner Join
(Select CMPIMGSIZESDT.CISCODE,
CMPIMGSIZESDT.CRDB,
TRNS.LCODE,
CMPIMGSIZESDT.MSIGN,
Sum(TRNS.AMOUNT * (1 - TRNS.CRDB)) XRE,
Sum(TRNS.AMOUNT * TRNS.CRDB) PIS
From CMPIMGSIZESDT,
TRNS,
ARTS
Left Join
GLPAR On GLPAR.CMPCODE = ARTS.CMPCODE And GLPAR.MTYPE = ARTS.MTYPE
Where TRNS.CMPCODE = CMPIMGSIZESDT.CMPCODE And ARTS.ARTID = TRNS.ARTID And
ARTS.CMPCODE = TRNS.CMPCODE And TRNS.LCODE Like CMPIMGSIZESDT.LCODE + '%'
And ((GLPAR.TRNSTYPE In (0, 1, 2, 3) Or (ARTS.MTYPE In (0, 1, 2, 3))) And
(ARTS.CMPCODE = 126) And (( -2 In ( -2) And ARTS.INBRCODE Is Null) Or
ARTS.INBRCODE In ( -2)))
Group By CMPIMGSIZESDT.CISCODE, CMPIMGSIZESDT.CRDB, TRNS.LCODE,
CMPIMGSIZESDT.MSIGN
Union
Select CMPIMGSIZESDT.CISCODE,
CMPIMGSIZESDT.CRDB,
ALTRNS.LCODE,
CMPIMGSIZESDT.MSIGN,
Sum(ALTRNS.AMOUNT * (1 - ALTRNS.CRDB)) XRE,
Sum(ALTRNS.AMOUNT * ALTRNS.CRDB) PIS
From CMPIMGSIZESDT,
ALTRNS,
ALARTS,
ALPAR
Where ALTRNS.CMPCODE = CMPIMGSIZESDT.CMPCODE And ALARTS.ALARTID =
ALTRNS.ALARTID And ALARTS.CMPCODE = ALTRNS.CMPCODE And
ALPAR.MTYPE = ALARTS.MTYPE And ALPAR.CMPCODE = ALARTS.CMPCODE And
ALTRNS.LCODE Like CMPIMGSIZESDT.LCODE + '%' And ALPAR.TRNSTYPE In (0, 1,
2, 3) And ALARTS.CMPCODE = 126
Group By CMPIMGSIZESDT.CISCODE, CMPIMGSIZESDT.CRDB, ALTRNS.LCODE,
CMPIMGSIZESDT.MSIGN) A On A.CISCODE = CMPIMGSIZESDT.CISCODE
Where CMPIMGSIZESDT.CMPCODE = 126
Group By CMPIMGSIZESCATDT.CISCODE, CMPIMGSIZES.DESCR, CMPIMGGENCAT.DESCR,
CMPIMGSIZESCAT.DESCR

Hello everybody ,I'm trying to get results from this script but its all wrong ! The AMT is the field with number but it is repeated multiple times ! Please help !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-11 : 06:23:21

can you show some sample data and explain your issue?
We cant understand anything from your above query as we dont know how data exists in table and also whats the output you're trying to get.

follow below guideline for posting the data

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nvoyatzopoulos
Starting Member

7 Posts

Posted - 2013-03-11 : 06:34:13

ciscode amt
4 9684,49
7 6701,05
15 5045,6
25 7873,4
29 1199,3
30 1811,09
57 17,56
63 111,39
64 210
72 60
73 57,2
136 2371,65

this the result i want to get with some other information (descr) . the strange think is that some nymber are correct and some other not .(it multiplies them)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-11 : 06:36:07

please follow the link and post data in required easy consumable format as insert statements. include sample data as well as your required output.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources