How to use Group By based on Case statement in Oracle?

Question

I have an SQL query where I use Oracle CASE to compare if the date column is less than or greater than the current one. But how do I use CASE -statement in GROUP BY -statement? I would like to read the entries in each case.

eg.

 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 I try: ORA-00904 . Any suggestions?

Answer 1

 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) 

Answer 2

Use inline view:

 SELECT expired, count(*) FROM (SELECT (CASE WHEN exp_date > SYSDATE THEN 1 WHEN exp_date <= SYSDATE THEN 2 ELSE 3 END) AS expired FROM mytable) GROUP BY expired; 

Hope this helps ...

Answer 3

As an additional input, although it is not associated with this stream, you can also aggregate your case statements without mentioning this in the group by section.

SELECT WORK_ORDER_NUMBER, SUM (CASE WHEN STOCK_CODE LIKE 'xxxx' THEN STOCK_QUANTITY ELSE 0 END) AS TOTAL From the GROUP BY WORK_ORDER_NUMBER table;

Please JK.