Oracle SQL multiple count query

Question

There is an Employee table with data like:

ID    FIRST_NAME     END_DATE                      CITY
23    Manoj       24-JUL-16 08.45.02.000000 AM    Bangalore
22    Abhishek    24-JUL-16 08.45.01.000000 AM    Bangalore
24    Nilu        24-JUL-16 08.46.01.000000 AM    Bangalore
25    Niroj       24-JUL-16 12.08.43.000000 PM    Bangalore
26    Tulu        24-JUL-16 10.47.01.000000 AM    Bangalore
29    Prashant    24-JUL-16 10.50.01.000000 AM    Bangalore
27    Tulu        24-JUL-16 01.32.01.000000 AM    Chennai
28    Panjvir     24-JUL-16 09.50.01.000000 AM    Bangalore

I need results like group by city and number the records for last minute, last sec, last hour and today with comparing to end date.

I am able to get the results with this query:

select e1.city,
       (select count(*) from Employee where end_date > (sysdate - interval '1' minute) and city = e1.city) as las_min,
       (SELECT count(*) FROM Employee WHERE end_date > (sysdate - interval '1' hour) and city = e1.city) as last_hours,
       (select count(*) from Employee where TRUNC(end_date) <= sysdate and city = e1.city) as today,
       (select count(*) from Employee where end_date between add_months(trunc(sysdate,'mm'),-1) and last_day(add_months(trunc(sysdate,'mm'),-1)) and city = e1.city) as last_months
 from Employee e1 group by e1.city;

Is there any better way to get the same results?

Answer 1

Consider using conditional aggregations to avoid the multiple subqueries:

SELECT e1.city,
    SUM(CASE end_date > (sysdate - interval '1' minute) THEN 1 ELSE NULL END) as las_min,
    SUM(CASE end_date > (sysdate - interval '1' hour) THEN 1 ELSE NULL END) as last_hours,
    SUM(CASE TRUNC(end_date) <= sysdate THEN 1 ELSE NULL END) as today,
    SUM(CASE end_date BETWEEN add_months(trunc(sysdate,'mm'),-1) 
        AND last_day(add_months(trunc(sysdate,'mm'),-1)) THEN 1 ELSE NULL END) as last_months
FROM Employee e1 
GROUP BY e1.city;

And even shorter, sum the logical expressions:

SELECT e1.city,
       SUM(end_date > (sysdate - interval '1' minute)) as las_min,
       SUM(end_date > (sysdate - interval '1' hour)) as last_hours,
       SUM(TRUNC(end_date) <= sysdate) as today,
       SUM(end_date BETWEEN add_months(trunc(sysdate,'mm'),-1) 
           AND last_day(add_months(trunc(sysdate,'mm'),-1))) as last_months
FROM Employee e1 
GROUP BY e1.city;