Group SUM into aliases

[code green]

I am trying to group stock levels into four columns depending on their location
This is difficult to put in words so this bit of pseudo code may explain better

SELECT part, descr,
CASE WHEN loc NOT IN ('Y1','Y2','F1','F2','T1','T2',) THEN SUM(stock) AS local,
CASE WHEN loc IN ('Y1','Y2',) THEN SUM(stock) AS Yard,
CASE WHEN loc IN ('F1''F2') THEN SUM(stock) AS Field,
CASE WHEN loc IN ('T1','T2') THEN SUM(stock) AS Transit,

I wrote this code then realised it would simply return the same figures every time.
Can any suggest how to do this better?

[NeoPa]

I would expect the following to work :

SELECT [part]
     , [descr]
     , SUM(CASE WHEN [loc] NOT IN ('Y1','Y2','F1','F2','T1','T2') THEN [stock] ELSE 0) AS local
     , SUM(CASE WHEN [loc] IN ('Y1','Y2') THEN [stock] ELSE 0) AS [Yard]
     , SUM(CASE WHEN [loc] IN ('F1','F2') THEN [stock] ELSE 0) AS [Field]
     , SUM(CASE WHEN [loc] IN ('T1','T2') THEN [stock] ELSE 0) AS [Transit]

[code green]

Hi NeoPa. I have been playing around with something similar; because an aggregate funtion (SUM) is used then part and loc has to be in a GROUP BY.
As soon as 'GROUP BY loc' is used, a seperate record for each location is created and the SUM(stock) just shows the stock in that location, rather than aggregating the stock into location groups.

[NeoPa]

My understanding is somewhat different. If an item is used within an aggregate function then it must not be included in the GROUP BY clause. Any item referred to outside of aggregation must be.

As such, I would expect to see [part] and [descr] in the GROUP BY clause but not [loc] or [stock], and certainly no smoking barrels (:D).

What results do you get when you try it?

[NeoPa]

I did some testing on a table in my database and it seemed to work fine, except I had the syntax a little wrong. I'd missed off the END keyword of each CASE statement. It should read :

SELECT [part]
     , [descr]
     , SUM(CASE WHEN [loc] NOT IN ('Y1','Y2','F1','F2','T1','T2') THEN [stock] ELSE 0 END) AS local
     , SUM(CASE WHEN [loc] IN ('Y1','Y2') THEN [stock] ELSE 0 END) AS [Yard]
     , SUM(CASE WHEN [loc] IN ('F1','F2') THEN [stock] ELSE 0 END) AS [Field]
     , SUM(CASE WHEN [loc] IN ('T1','T2') THEN [stock] ELSE 0 END) AS [Transit]

[code green]

"Column 'bins.loc' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause"

That is using this piece of code

SELECT RTRIM(LTRIM(part)) part, descr,
CASE WHEN loc NOT IN ('Y1','Y2','F1','F2','T1','T2') THEN SUM(stock) END AS local,
CASE WHEN loc IN ('Y1','Y2')  THEN SUM(stock) END AS Yard, 
CASE WHEN loc IN ('F1''F2') THEN SUM(stock) AS Field,
CASE WHEN loc IN ('T1','T2') THEN SUM(stock) AS Transit, 
FROM bins 
GROUP BY part, descr, --loc
ORDER BY part

If I un-edit 'loc', then the correct stock is aligned under the correct heading, but there is an entry for each location.

part   local
N0026	1515.0000
N0026	31.0000

Because the two figures are in seperate locations they are not aggregated

[NeoPa]

That doesn't seem very like the code I suggested. For the record, I would certainly not expect that to work, with or without [loc] in the GROUP BY clause. Let me take exactly what you've posted and put my version in then you can copy/paste the suggested code in and test it :

SELECT   RTRIM(LTRIM(part)) part, descr,
         SUM(CASE WHEN [loc] NOT IN ('Y1','Y2','F1','F2','T1','T2') THEN [stock] ELSE 0 END) AS local,
         SUM(CASE WHEN [loc] IN ('Y1','Y2') THEN [stock] ELSE 0 END) AS Yard, 
         SUM(CASE WHEN [loc] IN ('F1','F2') THEN [stock] ELSE 0 END) AS Field,
         SUM(CASE WHEN [loc] IN ('T1','T2') THEN [stock] ELSE 0 END) AS Transit
FROM     [bins]
GROUP BY [part], [descr]
ORDER BY [part]

[code green]

That doesn't seem very like the code I suggested

We posted minutes apart so I missed your suggestion. I was answering

What results do you get when you try it

CASE inside the SUM. I have never used that, so will give it a try

[code green]

Yes that does the trick.
Good thinking NeoPa

[NeoPa]

Thanks CG. I'm pleased that worked for you.

BTW. I'm going to switch the Best Answer to post #7 as the earlier one (#2) was a bit faulty. It missed off the END of each CASE WHEN.

I'd rather anyone searching for a solution found the one that can work properly.