Unanswered: Can I use 'CASE' in an aggregate query ?
select entityid, sum(legcramt), sum(credtamt), max(yrgiving)
where uses = '0007'
group by entityid
having (sum(legcramt) >= 2500 or sum(credtamt) >= 2500)
or (sum(legcramt) >= 1000 and sum(legcramt) < 2500)
or (sum(credtamt) >= 1000 and sum(credtamt) < 2500)
order by entityid
I would like to assign a value to a amount indicator for each of the 3 conditions in the having clause in the query above. Can I use CASE to achieve this?
EX. if the sum amt is >= 2500 I would like to put 'G' in the amount indicator. If the sum amt is between 1000 and 2500 then I would like to put "L" in the amount indicator, so on and so forth..
, CASE WHEN SUM(legcramt) >= 2500
OR SUM(credtamt) >= 2500
WHEN SUM(legcramt) >= 1000
AND SUM(legcramt) < 2500
WHEN SUM(credtamt) >= 1000
AND SUM(credtamt) < 2500
ELSE NULL END AS Amount_Indicator
WHERE uses = '0007'
HAVING Amount_Indicator IS NOT NULL