title: how to get record count per day in last week (7 days) oracle
body: <p>hello i am using the following sql query to get <strong>records count per hour in last 24 hours</strong>:</p>
  
  <pre><code>WITH date_range
    AS (SELECT TRUNC(sysdate - (rownum/24),'HH24') as the_hour
          FROM dual
        CONNECT BY ROWNUM &lt;= 24),
       the_data
    AS (SELECT TRUNC(systemdate, 'HH24')  AS log_date, count(*) AS num_obj
          FROM transactionlog where merchantcode='merc0003'
        GROUP BY TRUNC(systemdate, 'HH24'))   
  SELECT TO_CHAR(dr.the_hour,'DD/MM/YYYY HH:MI AM'), NVL(trans_log.num_obj,0)
  FROM date_range dr LEFT OUTER JOIN the_data  trans_log
       ON trans_log.log_date =  dr.the_hour
  ORDER BY dr.the_hour DESC ;  
  </code></pre>
  
  <p>I am trying to get <strong>record count per day in oracle for last 7 days</strong> .. can some one guide what i may do to get last 7 days by changing above query?</p>
  
  <p>i tried following to get last 7 days, but no gain as yet:</p>
  
  <pre><code>WITH date_range
    AS
  (SELECT TRUNC(sysdate - (7)) as the_hour
          FROM dual
        CONNECT BY ROWNUM &lt;= 7),
       the_data
    AS (SELECT TRUNC(systemdate, 'HH24')  AS log_date, count(*) AS num_obj
          FROM transactionlog where merchantcode='merc0003'
        GROUP BY TRUNC(systemdate, 'HH24'))   
  SELECT TO_CHAR(dr.the_hour,'DD/MM/YYYY HH:MI AM'), NVL(trans_log.num_obj,0)
  FROM date_range dr LEFT OUTER JOIN the_data  trans_log
       ON trans_log.log_date =  dr.the_hour
  ORDER BY dr.the_hour DESC ; 
  </code></pre>