Oracle SQL multiple count query

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)

Is there any better way to get the same results?

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;

StackExchange Code Review Q#135744, answer score: 5