Q:

Various Sums, grouped by Establishment Only

I have the following query, and I can't figure out how to get the desired result set to display.
I need this exact information, but grouped by Establishment ONLY, so that each row will have an Establishment and an appropriate sum and counts for that specific establishment. My query gives me all the correct numbers, except that it's also making me group by typeg, wdl and wdlr, which creates multiple rows for each establishment .
SELECT
    '2014' ,
    t6.Establishment AS 'establishment id' ,
    CASE WHEN wdl > 0 THEN COUNT(wdl) END AS 'Days away cases' ,
    CASE WHEN wdlr > 0 THEN COUNT(wdlr) END AS 'Days transfer cases' ,
    SUM(WDL) AS 'days away',
    SUM(wdlr) AS 'transfer days',
    CASE WHEN typeG = 1 THEN COUNT(typeG) END AS 'Injury Count'
FROM
    cairs.gics t1
LEFT JOIN 
    lkuEstablishment t6 ON t1.org = t6.org
WHERE
    Establishment IS NOT NULL
GROUP BY
    Establishment ,
    t1.typeG ,
    t1.WDL ,
    t1.WDLR;

A:

You are not using the CASE expressions correctly. You should use them inside the aggregration functions:
SELECT  '2014',
        t6.Establishment as [establishment id], 
        SUM(CASE WHEN wdl > 0 THEN 1 ELSE 0 END) as [Days away cases],
        SUM(CASE WHEN wdlr > 0 THEN 1 ELSE 0 END) as [Days transfer cases],
        SUM(WDL) as [days away], 
        SUM(wdlr) as [transfer days],
        SUM(CASE WHEN typeG = 1 THEN 1 ELSE 0 END) as [Injury Count]
FROM cairs.gics t1
LEFT JOIN lkuEstablishment t6 
    ON t1.org = t6.org
WHERE Establishment IS NOT NULL
GROUP BY t6.Establishment;

