[FIXED] getting multiple counts in SQL

Issue

I want my sql query to return me count of all active and past cases. I tried a query like this,

SELECT 
    sum(case when need_By > sysdate and status not in (30,40,50) then 1 else 0 end) AS active,
    sum(case when need_by < sysdate and status not in (30,40,50) then 1 else 0 end) AS past
FROM discrepancy
GROUP BY id;

This gives me output as follows:

ACTIVE PAST
0      1
1      0
0      0
0      1
0      1

What i am expecting is ,

ACTIVE PAST
  1      3

How should I change the query to get the count of active and past records. Also this query will be executed in hibernate and so want to make sure the createNativeQuery works .

Solution

Remove the GROUP BY:

SELECT sum(case when need_By > sysdate and status not in (30,40,50) then 1 else 0 end) AS active,
       sum(case when need_by < sysdate and status not in (30,40,50) then 1 else 0 end) AS past
FROM discrepancy;

MySQL also has a convenient short-hand notation so you can count the boolean expressions directly:

SELECT SUM(need_By > sysdate and status not in (30,40,50)) AS active,
       SUM(need_by < sysdate and status not in (30,40,50)) AS past
FROM discrepancy;

And finally, you can move the NOT IN to a WHERE clause which could improve performance:

SELECT SUM(need_By > sysdate) AS active,
       SUM(need_by < sysdate) AS past
FROM discrepancy
WHERE status NOT IN (30, 40, 50);

EDIT:

In Oracle, the last would be:

SELECT SUM(CASE WHEN need_By > sysdate THEN 1 ELSE 0 END) AS active,
       SUM(CASE WHEN need_by < sysdate THEN 1 ELSE 0 END) AS past
FROM discrepancy
WHERE status NOT IN (30, 40, 50);

Answered By - Gordon Linoff