3
\$\begingroup\$

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?

\$\endgroup\$
5
\$\begingroup\$

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;
| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ when i tried to execute above 2 queries getting: ORA-00907: missing right parenthesis \$\endgroup\$ – Abhishek Nayak Jul 26 '16 at 19:13
  • \$\begingroup\$ I see parentheses adding up. Try taking out a column one at at time to find issue. \$\endgroup\$ – Parfait Jul 31 '16 at 4:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.