group by issue - 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

group by issue

Author

Topic

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-01-04 : 08:58:43

Hi i have a sql stored procedure, which is below

i cant get it to group properly, the first column is "stockcode"

i want to only display a distinct stockcode, i thought group by would work but it dosnt,

can someone help me thanks.

select
LW.OL_St_Code, SUM(OL_Qty)as TotalQty, (Alo_Qty_Used)AS AlloLeft,
sum(case when MONTH(O_Date) = 1 then LW.OL_Qty else 0 end) as Jan,
sum(case when MONTH(O_Date) = 2 then LW.OL_Qty else 0 end) as Feb,
sum(case when MONTH(O_Date) = 3 then LW.OL_Qty else 0 end) as Mar,
sum(case when MONTH(O_Date) = 4 then LW.OL_Qty else 0 end) as Apr,
sum(case when MONTH(O_Date) = 5 then LW.OL_Qty else 0 end) as May,
sum(case when MONTH(O_Date) = 6 then LW.OL_Qty else 0 end) as June,
sum(case when MONTH(O_Date) = 7 then LW.OL_Qty else 0 end) as July,
sum(case when MONTH(O_Date) = 8 then LW.OL_Qty else 0 end) as Aug,
sum(case when MONTH(O_Date) = 9 then LW.OL_Qty else 0 end) as Sept,
sum(case when MONTH(O_Date) = 10 then LW.OL_Qty else 0 end) as Oct,
sum(case when MONTH(O_Date) = 11 then LW.OL_Qty else 0 end) as Nov,
sum(case when MONTH(O_Date) = 12 then LW.OL_Qty else 0 end) as Dec
from dbo.X_TBL_ORDER_LINEWEB LW

LEFT JOIN dbo.X_TBL_ORDERWEB OW ON OW.O_ID = LW.OL_O_ID AND YEAR(OW.O_Date) = @year1
LEFT JOIN dbo.X_TBL_AllocationToUser AT ON Alo_Stock_Code = LW.OL_St_Code

GROUP BY LW.OL_St_Code, Alo_Qty_Used
ORDER BY LW.OL_St_Code

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2008-01-04 : 09:02:59

it's not unique at stockcode level because the group by is at stockcode and alo_qty_used level so it is returning distinct stockcode and alo_qty_used records.

you could remove the alo_qty_used from the select statement and the group by statement or you could sum it and remove it from the group by statement, but it depends on what you want returned.

Duane.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-04 : 09:08:28

whats your exact requirement?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-01-04 : 11:00:58

My guess: Don't group on Alo_Qty_Used. Remove that column from your GROUP BY clause.

- Jeff
http://weblogs.sqlteam.com/JeffS

craigmac999
Starting Member

14 Posts

Posted - 2008-01-04 : 17:18:46

ok many thanks,

what i need returned is

a sum of alo_qty_used which i have tried sum(alo_qty_used)as qty3
but i get a massive number!!

the sum of alo_qty_used need to be a sum for each stock code, so i would get

distinct stock code1 - sum alo_qty_used - then my months here
distinct stock code2 - sum alo_qty_used - then my months here
distinct stock code3 - sum alo_qty_used - then my months here

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-01-04 : 18:38:25

jsmith is correct, remove the group by for Alo_qty_used. By grouping by that, you are removing the SUM aggregate, since the grouping will be for each individual alo_qty_used.

Poor planning on your part does not constitute an emergency on my part.

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-01-06 : 04:00:43

i have tried that but i get a agregate function error, is there another way?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-06 : 04:21:12

quote:
Originally posted by craigmacca

i have tried that but i get a agregate function error, is there another way?

select
LW.OL_St_Code, SUM(OL_Qty)as TotalQty, (Alo_Qty_Used)AS AlloLeft,
sum(case when MONTH(O_Date) = 1 then LW.OL_Qty else 0 end) as Jan,
sum(case when MONTH(O_Date) = 2 then LW.OL_Qty else 0 end) as Feb,
sum(case when MONTH(O_Date) = 3 then LW.OL_Qty else 0 end) as Mar,
sum(case when MONTH(O_Date) = 4 then LW.OL_Qty else 0 end) as Apr,
sum(case when MONTH(O_Date) = 5 then LW.OL_Qty else 0 end) as May,
sum(case when MONTH(O_Date) = 6 then LW.OL_Qty else 0 end) as June,
sum(case when MONTH(O_Date) = 7 then LW.OL_Qty else 0 end) as July,
sum(case when MONTH(O_Date) = 8 then LW.OL_Qty else 0 end) as Aug,
sum(case when MONTH(O_Date) = 9 then LW.OL_Qty else 0 end) as Sept,
sum(case when MONTH(O_Date) = 10 then LW.OL_Qty else 0 end) as Oct,
sum(case when MONTH(O_Date) = 11 then LW.OL_Qty else 0 end) as Nov,
sum(case when MONTH(O_Date) = 12 then LW.OL_Qty else 0 end) as Dec
from dbo.X_TBL_ORDER_LINEWEB LW

LEFT JOIN dbo.X_TBL_ORDERWEB OW ON OW.O_ID = LW.OL_O_ID AND YEAR(OW.O_Date) = @year1
LEFT JOIN dbo.X_TBL_AllocationToUser AT ON Alo_Stock_Code = LW.OL_St_Code

GROUP BY LW.OL_St_Code, Alo_Qty_Used
ORDER BY LW.OL_St_Code

You have to apply aggregate function on field in blue if you are removing Alo_Qty_Used from group by (like MIN or MAX)

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-01-06 : 05:30:49

ok yes i need a sum of the column, so i tried sum but i get a massive number which is not correct

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-06 : 05:36:40

That depends on your requirement. Thats why i asked what was your complete requirement earlier. You may use MIN(Alo_Qty_Used),MAX(Alo_Qty_Used),SUM(Alo_Qty_Used),...as per your need corresponding to each LW.OL_St_Code.

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-01-06 : 05:40:29

ok yes i need a sum of the column, so i tried sum but i get a massive number which is not correct, but it does group by the right thing, just the "Alo_Qty_Used" column gives me a number of 300 when it should be 30

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-06 : 05:50:11

Can you specify your requirement clearly? Are you looking for sum of quantities for each LW.OL_St_Code? also can you make sure the left joins arent causing the qty to be duplicated? Where is this Alo_Qty_Used taken from?

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-01-06 : 06:06:10

ok yes the (Alo_Qty_Used)AS AlloLeft comes from dbo.X_TBL_AllocationToUser so should be (AT.Alo_Qty_Used)AS AlloLeft

so it must be the left join but not sure what else to do?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-06 : 06:11:51

ok. Then that shouldnt be a problem. Anyways, can you try putting SUM(DISTINCT Alo_Qty_Used) instead of SUM(Alo_Qty_Used)and see if value is coming as per your expectation?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources