GROUP BY problem on WHEN in CASE statement

I’m trying to have the indirect and direct hours all show on one row and I know multiple rows are showing up because of the group by statement. However, I’ve tried removing the grouping and get an error that the l.CHG_TYPE is not included in an aggregate when removed.

<html><table><body><tr><th>ID</th><th>Billing Period</th><th>Shop</th><th>Shift Code</th><th>Employee #</th><th>Indirect Hours</th><th>Direct Hours</th></tr><tr class="odd"><td>202001404223396</td><td>202001</td><td>Sanitation</td><td>1st</td><td>23396</td><td>&nbsp;</td><td>12.73</td></tr>
<tr><td>202001404223396</td><td>202001</td><td>Sanitation</td><td>1st</td><td>23396</td><td>6.41</td><td>&nbsp;</td></tr>
<tr class="odd"><td>202001205225499</td><td>202001</td><td>Car Shop</td><td>1st</td><td>25499</td><td>&nbsp;</td><td>12.4</td></tr>
<tr><td>202001205225499</td><td>202001</td><td>Car Shop</td><td>1st</td><td>25499</td><td>6.82</td><td>&nbsp;</td></tr>
</table></body></html>

​x
 
<html><table><body><tr><th>ID</th><th>Billing Period</th><th>Shop</th><th>Shift Code</th><th>Employee #</th><th>Indirect Hours</th><th>Direct Hours</th></tr><tr class="odd"><td>202001404223396</td><td>202001</td><td>Sanitation</td><td>1st</td><td>23396</td><td>&nbsp;</td><td>12.73</td></tr><tr><td>202001404223396</td><td>202001</td><td>Sanitation</td><td>1st</td><td>23396</td><td>6.41</td><td>&nbsp;</td></tr><tr class="odd"><td>202001205225499</td><td>202001</td><td>Car Shop</td><td>1st</td><td>25499</td><td>&nbsp;</td><td>12.4</td></tr><tr><td>202001205225499</td><td>202001</td><td>Car Shop</td><td>1st</td><td>25499</td><td>6.82</td><td>&nbsp;</td></tr></table></body></html>

SELECT
    CONCAT(l.FISC_PD, l.LOCATION, l.SHIFT_CODE, e.EmpNo) 'ID',
    l.FISC_PD 'Billing Period', 
    CASE
        WHEN l.LOCATION = '202' THEN 'Specialty Equipment' 
        WHEN l.LOCATION = '205' THEN 'Car Shop'
        WHEN l.LOCATION = '206' THEN 'Truck Shop'
        WHEN l.LOCATION = '404' THEN 'Sanitation'
        WHEN l.LOCATION = '212' THEN 'Tire Shop'
        ELSE l.LOCATION
    END as 'Shop',
    CASE
        WHEN l.SHIFT_CODE in ('2','6') THEN '1st'
        WHEN l.SHIFT_CODE in ('3','4','5') THEN '2nd'
        ELSE NULL
    END as 'Shift Code',
    e.EmpNo 'Employee #',
    CASE
        WHEN l.CHG_TYPE = 'I' THEN ROUND((SUM(l.DURATION)/36000000),2)
        ELSE NULL
    END as 'Indirect Hours',
    CASE
        WHEN l.CHG_TYPE = 'D' THEN ROUND((SUM(l.DURATION)/36000000),2)
        ELSE NULL
    END as 'Direct Hours'
FROM m5prod.mfive.VIEW_EMP_LABOR_JNL l
LEFT JOIN m5prod.mfive.VIEW_ALL_EMPLOYEES e on e.EmpID = l.EMP_ID
WHERE 
    l.location not in ('218', '201')
    and l.FISC_PD in ('202001', '202002', '202003')
GROUP BY
    l.FISC_PD,  
    e.EmpNo,
    l.LOCATION,
    l.SHIFT_CODE,
    l.CHG_TYPE

 
SELECT    CONCAT(l.FISC_PD, l.LOCATION, l.SHIFT_CODE, e.EmpNo) 'ID',    l.FISC_PD 'Billing Period',     CASE        WHEN l.LOCATION = '202' THEN 'Specialty Equipment'         WHEN l.LOCATION = '205' THEN 'Car Shop'        WHEN l.LOCATION = '206' THEN 'Truck Shop'        WHEN l.LOCATION = '404' THEN 'Sanitation'        WHEN l.LOCATION = '212' THEN 'Tire Shop'        ELSE l.LOCATION    END as 'Shop',    CASE        WHEN l.SHIFT_CODE in ('2','6') THEN '1st'        WHEN l.SHIFT_CODE in ('3','4','5') THEN '2nd'        ELSE NULL    END as 'Shift Code',    e.EmpNo 'Employee #',    CASE        WHEN l.CHG_TYPE = 'I' THEN ROUND((SUM(l.DURATION)/36000000),2)        ELSE NULL    END as 'Indirect Hours',    CASE        WHEN l.CHG_TYPE = 'D' THEN ROUND((SUM(l.DURATION)/36000000),2)        ELSE NULL    END as 'Direct Hours'FROM m5prod.mfive.VIEW_EMP_LABOR_JNL lLEFT JOIN m5prod.mfive.VIEW_ALL_EMPLOYEES e on e.EmpID = l.EMP_IDWHERE     l.location not in ('218', '201')    and l.FISC_PD in ('202001', '202002', '202003')GROUP BY    l.FISC_PD,      e.EmpNo,    l.LOCATION,    l.SHIFT_CODE,    l.CHG_TYPE​

Answer

You can use conditional aggregation as follows:

remove CHG_TYPE from the GROUP BY clause
move the CASE expression within the SUM()

SELECT
    CONCAT(l.FISC_PD, l.LOCATION, l.SHIFT_CODE, e.EmpNo) 'ID',
    l.FISC_PD 'Billing Period', 
    CASE
        WHEN l.LOCATION = '202' THEN 'Specialty Equipment' 
        WHEN l.LOCATION = '205' THEN 'Car Shop'
        WHEN l.LOCATION = '206' THEN 'Truck Shop'
        WHEN l.LOCATION = '404' THEN 'Sanitation'
        WHEN l.LOCATION = '212' THEN 'Tire Shop'
        ELSE l.LOCATION
    END as 'Shop',
    CASE
        WHEN l.SHIFT_CODE in ('2','6') THEN '1st'
        WHEN l.SHIFT_CODE in ('3','4','5') THEN '2nd'
        ELSE NULL
    END as 'Shift Code',
    e.EmpNo 'Employee #',
    ROUND(SUM(CASE WHEN l.CHG_TYPE = 'I' THEN l.DURATION END)/36000000),2) as 'Indirect Hours',
    ROUND(SUM(CASE WHEN l.CHG_TYPE = 'D' THEN l.DURATION END)/36000000),2) as 'Direct Hours'
FROM m5prod.mfive.VIEW_EMP_LABOR_JNL l
LEFT JOIN m5prod.mfive.VIEW_ALL_EMPLOYEES e on e.EmpID = l.EMP_ID
WHERE 
    l.location not in ('218', '201')
    and l.FISC_PD in ('202001', '202002', '202003')
GROUP BY
    l.FISC_PD,  
    e.EmpNo,
    l.LOCATION,
    l.SHIFT_CODE

 
SELECT    CONCAT(l.FISC_PD, l.LOCATION, l.SHIFT_CODE, e.EmpNo) 'ID',    l.FISC_PD 'Billing Period',     CASE        WHEN l.LOCATION = '202' THEN 'Specialty Equipment'         WHEN l.LOCATION = '205' THEN 'Car Shop'        WHEN l.LOCATION = '206' THEN 'Truck Shop'        WHEN l.LOCATION = '404' THEN 'Sanitation'        WHEN l.LOCATION = '212' THEN 'Tire Shop'        ELSE l.LOCATION    END as 'Shop',    CASE        WHEN l.SHIFT_CODE in ('2','6') THEN '1st'        WHEN l.SHIFT_CODE in ('3','4','5') THEN '2nd'        ELSE NULL    END as 'Shift Code',    e.EmpNo 'Employee #',    ROUND(SUM(CASE WHEN l.CHG_TYPE = 'I' THEN l.DURATION END)/36000000),2) as 'Indirect Hours',    ROUND(SUM(CASE WHEN l.CHG_TYPE = 'D' THEN l.DURATION END)/36000000),2) as 'Direct Hours'FROM m5prod.mfive.VIEW_EMP_LABOR_JNL lLEFT JOIN m5prod.mfive.VIEW_ALL_EMPLOYEES e on e.EmpID = l.EMP_IDWHERE     l.location not in ('218', '201')    and l.FISC_PD in ('202001', '202002', '202003')GROUP BY    l.FISC_PD,      e.EmpNo,    l.LOCATION,    l.SHIFT_CODE​

Side note: it is not a good practice to use single quotes for column aliases. In standard SQL, single quotes are reserved for string litterals. I would suggest using the relevant quote character for your database (MySQL: backticks – SQL Server: square brackets – Postgres and Oracle: double quotes). Better yet, you can use column aliases that do not contain special characters, so they don’t need to be quoted.

Advertisement

Answer