current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 3 down vote favorite

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?

share|improve this question
    
Check out this question from StackOverflow: stackoverflow.com/questions/6356564/… – Daniel Jul 24 '16 at 8:20
up vote 5 down vote

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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