CODEWITHSUNDEEP
sqloracle-databasecount
user906153
user906153

Reputation: 1218

Get the count of multiple tables broken up by hour

I have two tables, QUEUE and TRACKER that I would like to get the counts for yesterday. Each table has an INSERT_DATE field that indicates when that row was inserted.

Right now, I am using two queries to get this data. This is the query I am using to get the count for TRACKER

SELECT TO_CHAR(TRUNC(insert_date,'HH'),'YYYY-MM-DD HH24:MI:SS') AS Hour_Inserted,
       COUNT(*) AS Records_Inserted
  FROM TRACKER
 WHERE     insert_date >= TRUNC(SYSDATE - 1) 
       AND insert_date <  TRUNC(SYSDATE)
GROUP BY 
       TRUNC(insert_date,'HH')
ORDER BY 
       Hour_Inserted

I use the same query to get the count for QUEUE, I just switch the table name.

   SELECT TO_CHAR(TRUNC(insert_date,'HH'),'YYYY-MM-DD HH24:MI:SS') AS Hour_Inserted,
           COUNT(*) AS Records_Inserted
      FROM QUEUE
     WHERE     insert_date >= TRUNC(SYSDATE - 1) 
           AND insert_date <  TRUNC(SYSDATE)
    GROUP BY 
           TRUNC(insert_date,'HH')
    ORDER BY 
           Hour_Inserted

I would like to condense this down into one query, instead of having to run two seperate ones.

What would this new query look like?

Ideally, the result of the query would look something like this:

Hour_Inserted        | Queue_Records | Tracker_Records
----------------------------------------------------
2014-03-12 00:00:00     56                 102
2014-03-12 01:00:00     80                 200

Upvotes: 0

Views: 93

Answers (1)

Yaroslav Shabalin
Yaroslav Shabalin

Reputation: 1644

If you want to combine the output, you need UNION:

SELECT 'TRACKER' data_type,
       TO_CHAR(TRUNC(insert_date, 'HH'), 'YYYY-MM-DD HH24:MI:SS') AS Hour_Inserted,
       COUNT(*) AS Records_Inserted
  FROM TRACKER
 WHERE insert_date >= TRUNC(SYSDATE - 1)
   AND insert_date < TRUNC(SYSDATE)
 GROUP BY TRUNC(insert_date, 'HH')
UNION ALL
SELECT 'QUEUE' data_type,
       TO_CHAR(TRUNC(insert_date, 'HH'), 'YYYY-MM-DD HH24:MI:SS') AS Hour_Inserted,
       COUNT(*) AS Records_Inserted
  FROM QUEUE
 WHERE insert_date >= TRUNC(SYSDATE - 1)
   AND insert_date < TRUNC(SYSDATE)
 GROUP BY TRUNC(insert_date, 'HH')
 ORDER BY data_type, Hour_Inserted

UPDATE: Due to updated requirements there is another query:

select coalesce(tracker.hour_inserted, queue.hour_inserted) hour_inserted,
       tracker.records_inserted tracker_records_inserted,
       queue.records_inserted queue_records_inserted
  from (SELECT TO_CHAR(TRUNC(insert_date, 'HH'), 'YYYY-MM-DD HH24:MI:SS') AS Hour_Inserted,
               COUNT(*) AS Records_Inserted
          FROM TRACKER
         WHERE insert_date >= TRUNC(SYSDATE - 1)
           AND insert_date < TRUNC(SYSDATE)
         GROUP BY TRUNC(insert_date, 'HH')) tracker
  FULL OUTER JOIN (SELECT TO_CHAR(TRUNC(insert_date, 'HH'),
                                  'YYYY-MM-DD HH24:MI:SS') AS Hour_Inserted,
                          COUNT(*) AS Records_Inserted
                     FROM QUEUE
                    WHERE insert_date >= TRUNC(SYSDATE - 1)
                      AND insert_date < TRUNC(SYSDATE)
                    GROUP BY TRUNC(insert_date, 'HH')) queue on tracker.hour_inserted =
                                                                queue.hour_inserted
 ORDER BY 1;

Link to SQLFiddle.

Upvotes: 1

Related Questions