SQLTeam.com | Weblogs | Forums

Aggregate error and summary (group by)

I want to summarize by project number but want to use another variable in a case statement. I keep getting aggregate error, until I add TASKNO_X TO THE GROUP BY. ... then it summarizes at the taskno level too.

SELECT
PA_COST_VIEW.PROJECTNO_X,
SUM(TRANSAMNT_N) AS SUMQTY,
CASE WHEN PA_COST_VIEW.TASKNO_X = '000000' THEN SUM(TRANSAMNT_N) ELSE 0 END AS QTY000,
CASE WHEN PA_COST_VIEW.TASKNO_X = '000001' THEN SUM(TRANSAMNT_N) ELSE 0 END AS QTY001,
CASE WHEN SUBSTRING(PA_COST_VIEW.TASKNO_X,1,5) <> '00000' THEN SUM(TRANSAMNT_N) ELSE 0 END AS QTY002

FROM PA_COST_VIEW, GL_DETAIL
WHERE PA_COST_VIEW.PA_COST_ID = GL_DETAIL.PA_COST_ID { AND
PA_COST_VIEW.ENTITY_ID = ?ENTITY_ID?}
GROUP BY PROJECTNO_X, TASKNO_X
ORDER BY PROJECTNO_X

Put the CASE inside the SUM, like this:

SELECT
PA_COST_VIEW.PROJECTNO_X,
SUM(TRANSAMNT_N) AS SUMQTY,
SUM(CASE WHEN PA_COST_VIEW.TASKNO_X = '000000' THEN TRANSAMNT_N ELSE 0 END) AS QTY000,
SUM(CASE WHEN PA_COST_VIEW.TASKNO_X = '000001' THEN TRANSAMNT_N ELSE 0 END) AS QTY001,
SUM(CASE WHEN SUBSTRING(PA_COST_VIEW.TASKNO_X,1,5) <> '00000' THEN TRANSAMNT_N ELSE 0 END) AS QTY002
FROM PA_COST_VIEW, GL_DETAIL
WHERE PA_COST_VIEW.PA_COST_ID = GL_DETAIL.PA_COST_ID { AND
PA_COST_VIEW.ENTITY_ID = ?ENTITY_ID?}
GROUP BY PROJECTNO_X
ORDER BY PROJECTNO_X

Thank you so much!.