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?

• Daniel: Check out this question from StackOverflow: stackoverflow.com/questions/6356564/…

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;

• Abhishek Nayak: when i tried to execute above 2 queries getting: ORA-00907: missing right parenthesis
• Parfait: I see parentheses adding up. Try taking out a column one at at time to find issue.