I have an SQL-query where I use Oracle CASE
to compare if a date column is less than or greater than current date. But how do I use that CASE
-statement in a GROUP BY
-statement? I would like to count the records in each case.
E.g.
select
(case
when exp_date > sysdate then 1
when exp_date <= sysdate then 2
else 3
end) expired, count(*)
from mytable
group by expired
But I get an error when trying this: ORA-00904
. Any suggestions?
select
(case
when exp_date > sysdate then 1
when exp_date <= sysdate then 2
else 3
end) expired, count(*)
from mytable
group by (case
when exp_date > sysdate then 1
when exp_date <= sysdate then 2
else 3
end)