Problems with GROUP BY and CASE statement

karim · September 22nd, 2004, 03:39 PM

I'm trying to get the query below to execute. The problem is that EL.CategoryID, apparently, has to be in the GROUP BY list or part of an aggregate function in the SELECT. Putting it in the GROUP BY list splits up records that should be combined so that isn't an option. I tried putting in a dummy MAX (EL.CategoryID) in the SELECT but that did nothing (suprisingly). I'm stumped partly because I assumed that I would only see this error if I actually tried to return an EL.CategoryID column in the SELECT which I'm not. Anyway, your thoughts would be very welcome at this point.

Code:

SELECT 
    P.ProjectID, 
    P.Name AS ProjectName,    
    (CASE 
        WHEN EL.CategoryID = '71' THEN Sum (EL.Duration)
        ELSE 0
    END) AS TotalCBHours,
    (CASE 
        WHEN EL.CategoryID = '72' THEN Sum (EL.Duration)
        ELSE 0
    END) AS TotalFPHours,
    (CASE 
        WHEN EL.CategoryID = '73' THEN Sum (EL.Duration)
        ELSE 0
    END) AS TotalNBHours,
    Sum(EL.Duration) AS TotalHours

FROM TT_Projects P INNER JOIN TT_EntryLog EL ON (P.ProjectID = EL.ProjectID AND EL.CategoryID IN ('71','72','73'))
GROUP BY P.ProjectID, P.Name

pgtips · September 23rd, 2004, 03:44 AM

move the SUM outside the CASE

Code:

SELECT 
    P.ProjectID, 
    P.Name AS ProjectName,    
    SUM(CASE WHEN EL.CategoryID = '71' THEN EL.Duration ELSE 0 END) AS TotalCBHours,
    ...

karim · September 23rd, 2004, 06:39 AM

That worked. Thank you *very* much. Just out of curiousity, does anyone know why putting in a dummy aggregate function that includes the EL.CategoryID doesn't work? As in:

Code:

SELECT
   MAX (EL.CategoryID),
   ...