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