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?