2 Replies Latest reply: Dec 31, 2012 4:54 PM by Brent Grech RSS

    Counting Multiple Occurences in Same Table Grouped by Date

    Brent Grech
      Hello,

      I am stuck on this one. I have a table that has many transactions. I would like to write a SQL that shows for a given date range, how many rows were added and how many were distinctly updated. In my table I have 2 timestamp columns that I am trying to count on:

      DATE_CREATED
      LAST_UPDATE_DATE

      I would like my report to look something like. I don't care if the updated column includes the rows created but I do care if there are no rows created and would like to set the value to 0.


      Date Number Rows Created Number of Rows Updated
      12/01/2013 10 15
      12/02/2013 0 10
      12/03/2013 30 36
      12/04/2013 50 70
      ...

      I cannot figure this out. I know it's a combination of CASE statements and am hoping to get it done with regular SQL

      I transform the timestamp with TO_CHAR to MM/DD/YYYY then group by. I have no problem getting one or the other with separate queries, but not both in the same table.

      Thank you!
      -Brent
        • 1. Re: Counting Multiple Occurences in Same Table Grouped by Date
          Frank Kulash
          Hi, Brent,

          Interesting problem! This clearly seems to be a GROUP BY problem, but what can you put in the GROUP BY clause? You need to do two separate COUNT functions, each with its own GROUP BY clause.

          I think you want to do something like this:
          WITH     created          AS
          (
               SELECT       TRUNC (date_created)          AS date_created
               ,       COUNT (*)               AS rows_created
               FROM       table_x
               GROUP BY  TRUNC (date_created)
          )
          ,     updated          AS
          (
               SELECT       TRUNC (last_update_date)     AS date_updated
               ,       COUNT (*)               AS rows_updated
               FROM       table_x
               GROUP BY  TRUNC (last_update_date)
          )
          SELECT    NVL ( c.date_created
                     , u.date_updated)          AS dml_date
          ,       NVL (c.rows_reated, 0)        AS rows_created
          ,       NVL (u.row_updated, 0)     AS rows_updated
          FROM              created  c
          FULL OUTER JOIN      updated  u  ON  u.date_updated  = c.date_created
          ORDER BY  dml_date
          ;
          If you wanted only the number of rows created, then you would run something like the first sub-query (created), right? And if you wanted only the number of rows updated, then you would run something like the second sub_query (updated). So getting them both at the same time is just a matter of joining those two result sets.


           

          I hope this answers your question.
          If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
          Explain, using specific examples, how you get those results from that data.
          Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
          See the forum FAQ {message:id=9360002}
          • 2. Re: Counting Multiple Occurences in Same Table Grouped by Date
            Brent Grech
            thank you!!! with some minor tweaks to the above code, i got it to work:

            WITH     created          AS
            (
                 SELECT     TRUNC (CREATION_DATE)          AS date_created
                 ,     COUNT (*)               AS rows_created
                 FROM     my_table
                 GROUP BY TRUNC (CREATION_DATE)
            )
            ,     updated          AS
            (
                 SELECT     TRUNC (last_update_date)     AS date_updated
                 ,     COUNT (*)               AS rows_updated
                 FROM     my_table
                 GROUP BY TRUNC (last_update_date)
            )
            SELECT NVL ( c.date_created
                 , u.date_updated)          AS DATA_DATE
            ,     NVL (c.rows_created, 0)      AS num_rows_created
            ,     NVL (u.rows_updated, 0)     AS num_rows_updated
            FROM     created c
            FULL OUTER JOIN     updated u ON u.date_updated = c.date_created
            ORDER BY DATA_DATE
            ;