CODEWITHSUNDEEP
sqloracle-database
Mark Harrison
Mark Harrison

Reputation: 304662

Summarizing counts by date

Suppose I have a table of dates.

select * from t;
d
-
2014/01/02
2014/02/05
2014/02/12
2014/04/01

What query will provide the total number of rows by month?

 2014/01  1
 2014/02  2
 2014/04  1

likewise, cumulative totals

2014/01  1
2014/02  3
2014/04  4

and finally, with zero values for months with no rows

2014/01  1
2014/02  2
2014/03  0
2014/04  1

An Oracle-specific solution is acceptable.

Upvotes: 1

Views: 79

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35613

Some slight variations to the well laid out answer by ajmalmhd04.

Here we use TRUNC() in the form of TRUNC(anydate,'MONTH') that returns the 1st day of the relevant month and it retains the data type which can be faster than working with strings in large data sets.

Additionally use of ANSI join syntax is used.

-- full monthly range over 12 months
-- example, 12 months starting at Jan 1 current year
SELECT ADD_MONTHS(TRUNC(sysdate,'YEAR'),LEVEL) range_month
FROM dual
CONNECT BY level <= 12

-- gather the base data

SELECT TO_CHAR(the_month,'yyyy-mm-dd') AS the_month, the_count
FROM (
     SELECT TRUNC(my_date,'MONTH') AS the_month, COUNT(*) AS the_count
     FROM t
     GROUP BY TRUNC(my_date,'MONTH')
     )
ORDER BY the_month
;

-- produce a cumulative

SELECT
       TO_CHAR(the_month,'yyyy-mm-dd') AS the_month
     , the_count
     , sum(the_count) over(order by the_month rows unbounded preceding) AS cumulative
FROM (
     SELECT TRUNC(my_date,'MONTH') AS the_month, COUNT(*) AS the_count
     FROM t
     GROUP BY TRUNC(my_date,'MONTH')
     )
ORDER BY the_month
;

-- combine range with base data & cumulative by left outer join

SELECT
       TO_CHAR(Y.range_month,'yyyy-mm-dd') AS the_month
     , NVL(T.the_count,0)                  AS the_count
     , sum(T.the_count) over(order by Y.range_month rows unbounded preceding) AS cumulative
FROM
     (
      SELECT ADD_MONTHS(TO_DATE('2014-01-01','yyyy-mm-dd'),LEVEL) range_month /* fixed date used for example only */
      FROM dual
      CONNECT BY level <= 12
     ) Y
LEFT OUTER JOIN (
                 SELECT TRUNC(my_date,'MONTH') AS the_month, COUNT(*) AS the_count
                 FROM t
                 GROUP BY TRUNC(my_date,'MONTH')
                ) T
                  ON Y.range_month = T.the_month
ORDER BY 
       Y.range_month
;

The above may be viewed at this SQLfiddle demo.

Upvotes: 1

ajmalmhd04
ajmalmhd04

Reputation: 2602

Assuming the table name as t with following records:

2014/01/02
2014/02/05
2014/02/12
2014/04/01

1)

SELECT *
FROM
     (SELECT TO_CHAR(my_date,'yyyy/mm')my_date, COUNT(*)
     FROM t
     GROUP BY TO_CHAR(my_date,'yyyy/mm')
     )
ORDER BY to_date(my_date,'yyyy/mm');

results:

2014/01 1
2014/02 2
2014/04 1

The following query: 2)

SELECT my_date, sum(my_count) over ( order by 1 rows unbounded preceding) cumulative
FROM
     (SELECT TO_CHAR(my_date,'yyyy/mm')my_date,
          COUNT(*) my_count
     FROM t
     GROUP BY TO_CHAR(my_date,'yyyy/mm')
     )
ORDER BY to_date(my_date,'yyyy/mm');

results cumulative count:

2014/01 1
2014/02 3
2014/04 4

3) for whole year:

SELECT b.all_date,nvl(my_count,0)my_count
FROM
     (SELECT TO_CHAR(my_date,'yyyy/mm')my_date,
          COUNT(*) my_count
     FROM t
     GROUP BY TO_CHAR(my_date,'yyyy/mm')
     )A,
     (SELECT TO_CHAR(add_months(to_date('2014/01/01','yyyy/mm/dd')-1 ,LEVEL),'yyyy/mm') all_date
     FROM dual
     CONNECT BY level <= 12
) b
WHERE A.mY_date(+) = b.all_date
ORDER BY to_date(b.all_date,'yyyy/mm');

results :

2014/01 1
2014/02 2
2014/03 0
2014/04 1
2014/05 0
2014/06 0
2014/07 0
2014/08 0
2014/09 0
2014/10 0
2014/11 0
2014/12 0

Upvotes: 3

Related Questions