Pivot Table (Last hurdle) 2

[mrliam69]

I am trying to group some quantities together but I keep getting stuck on the last stage....

SELECT     SM_STOCK_CODE, sm_type, YEAR(SM_DATE)AS smyear,  
CASE WHEN sm_type = 'P' THEN SUM(CASE WHEN sm_status = 'O' THEN 0 - sm_quantity ELSE sm_quantity END)ELSE 0 END AS Bought, 
CASE WHEN sm_type = 'A' THEN SUM(CASE WHEN sm_status = 'O' THEN 0 - sm_quantity ELSE sm_quantity END)ELSE 0 END AS Used, 
CASE WHEN sm_type = 'S' THEN SUM(CASE WHEN sm_status = 'O' THEN 0 - sm_quantity ELSE sm_quantity END) ELSE 0 END AS Sold
FROM         STK_MOVEMENTS
GROUP BY YEAR(SM_DATE), SM_STOCK_CODE, sm_type
HAVING      (SM_STOCK_CODE = 'efl052')

Which comes out like this


SM_STOCK_CODE sm_type smyear Bought Used Sold
EFL052 A 2004 0 -76 0
EFL052 A 2005 0 431 0
EFL052 A 2006 0 -55 0
EFL052 L 2004 0 0 0
EFL052 P 2004 76 0 0
EFL052 P 2005 761 0 0
EFL052 S 2005 0 0 679


And I want it to come out like this


SM_STOCK_CODE smyear Bought Used Sold
EFL052 2004 76 -76 0
EFL052 2005 761 431 679
EFL052 2006 0 -55 0


but I keep getting an aggregate error when I change the sql....

Ideas ?

 

[gmmastros]

I suggest you move the SUM aggregate to the 'outside' of the column. Like this...

[!]SUM([/!]CASE WHEN sm_type = 'P' THEN CASE WHEN sm_status = 'O' THEN 0 - sm_quantity ELSE sm_quantity END ELSE 0 END[!])[/!] AS Bought

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[PWise]

SELECT   SM_STOCK_CODE,  YEAR(SM_DATE)AS smyear, 
sum (case when sm_type = 'P' then CASE WHEN sm_status = 'O' then - sm_quantity else sm_quantity end else 0 end )AS Bought, 
sum (case when sm_type = 'A' then CASE WHEN sm_status = 'O' then - sm_quantity else sm_quantity end else 0 end )AS Used, 
sum (case when sm_type = 'S' then CASE WHEN sm_status = 'O' then - sm_quantity else sm_quantity end else 0 end )AS Sold
FROM         STK_MOVEMENTS
Where      (SM_STOCK_CODE = 'efl052') 
GROUP BY YEAR(SM_DATE), SM_STOCK_CODE

not tested

 

[mrliam69]

Cheers guys that worked a treat spot on with the code pwise