How to count number of rows returned per month in oracle?

Question

I am creating a feature for my application where I need to generate a report for the whole 2018.

I need to count all the tickets for 2018. Each ticket has a category. For example: Change of name. Senior Citizen etc.

I need to count the number of change name tickets, senior citizen tickets for 2018 per month

I tried to to this but I can't seem to get the result that I want.

I can't seem to break down the count per month.

This is the query that I have so far:

SELECT SUBCATEGORY,COUNT(ticket_no) 
  FROM CNR_TICKET 
 WHERE date_created >= TO_DATE('1/01/2018','MM/DD/YYYY') 
   AND date_created <= TO_DATE('12/31/2018','MM/DD/YYYY') 
 GROUP BY SUBCATEGORY;

This is the columns I want to see:

CATEGORY | JAN | FEB | MARCH | APRIL | MAY | JNE | JUL | AUG | SEPT | OCT| NOV| DEC
SENIOR      2     5      20      50     1     11    23    4      1     2    4    6
COAN        23   55      22      55     6      2    12    23    12     12   5   89

Please post your table structure, some sample data and expected output for that data — Pham X. Bach, Jan 14 '19 at 07:43

Indent · Answer 1 · 2019-01-14T08:19:31.560

Something like this :

SELECT
    SUBCATEGORY,    
    count( distinct case when EXTRACT(month FROM date_created) = 1  then ticket_no else null end) as JAN,
    count( distinct case when EXTRACT(month FROM date_created) = 2  then ticket_no else null end) as FEB,
    count( distinct case when EXTRACT(month FROM date_created) = 3  then ticket_no else null end) as MARCH,
    count( distinct case when EXTRACT(month FROM date_created) = 4  then ticket_no else null end) as APRIL,
    count( distinct case when EXTRACT(month FROM date_created) = 5  then ticket_no else null end) as MAY,
    count( distinct case when EXTRACT(month FROM date_created) = 6  then ticket_no else null end) as JNE,
    count( distinct case when EXTRACT(month FROM date_created) = 7  then ticket_no else null end) as JUL,
    count( distinct case when EXTRACT(month FROM date_created) = 8  then ticket_no else null end) as AUG,
    count( distinct case when EXTRACT(month FROM date_created) = 9  then ticket_no else null end) as SEPT,
    count( distinct case when EXTRACT(month FROM date_created) = 10 then ticket_no else null end) as OCT,
    count( distinct case when EXTRACT(month FROM date_created) = 11 then ticket_no else null end) as NOV,
    count( distinct case when EXTRACT(month FROM date_created) = 12 then ticket_no else null end) as DEC
FROM
    CNR_TICKET
WHERE
    date_created >= to_date('1/01/2018','MM/DD/YYYY') and
    date_created <= to_date('12/31/2018','MM/DD/YYYY')
GROUP BY
    SUBCATEGORY

you can change your WHERE clause using :

EXTRACT(year FROM date_created ) = 2018

Thanks! The count is adding 1-4 but it's already close to the actual results. — Patrick Brian Garcia, Jan 15 '19 at 09:11

Radim Bača · Answer 2 · 2019-01-14T08:41:44.163

1

You may try PIVOT statement

select * from (
    select SUBCATEGORY, month(date_created) mon
    from CNR_TICKET
    where date_created >= to_date('1/01/2018','MM/DD/YYYY') and date_created <= to_date('12/31/2018','MM/DD/YYYY')
)
pivot ( 
    count(*) 
    for mon
    in ( 1 Jan, 2 Feb, 3 MARCH, 4 APRIL, 5 MAY, 6 JNE, 7 JUL, 8 AUG, 9 SEPT, 10 OCT, 11 NOV, 12 DEC )
)

edited Jan 14 '19 at 08:41

answered Jan 14 '19 at 08:14

Radim Bača

10,646
1
19
33

Thanks! but this doesn't work for me, I think month function cannot be used in oracle – Patrick Brian Garcia Jan 15 '19 at 09:12

Barbaros Özhan · Answer 3 · 2019-01-14T08:59:48.373

you can use Pivot keyword by using for month for the pivoting query as

  select *
    from
    ( 
    select subcategory, to_char(date_created,'mm') as month
      from cnr_ticket 
     where to_char(date_created,'yyyy')='2018'
      ) 
     pivot(
           count(*)
           for (month)
            in ('01' as jan ,'02' as feb, '03' as mar,
                '04' as apr ,'05' as may, '06' as jun,
                '07' as jul ,'08' as aug, '09' as sep,
                '10' as oct ,'11' as nov, '12' as dec
               )
       )

or using conditional aggregation

    select subcategory,
           sum(case when to_char(date_created,'mm') = '01' then 1 else 0 end) as jan,
           sum(case when to_char(date_created,'mm') = '02' then 1 else 0 end) as feb,
           sum(case when to_char(date_created,'mm') = '03' then 1 else 0 end) as mar,
           sum(case when to_char(date_created,'mm') = '04' then 1 else 0 end) as apr,       
           sum(case when to_char(date_created,'mm') = '05' then 1 else 0 end) as may,
           sum(case when to_char(date_created,'mm') = '06' then 1 else 0 end) as jun,
           sum(case when to_char(date_created,'mm') = '07' then 1 else 0 end) as jul,
           sum(case when to_char(date_created,'mm') = '08' then 1 else 0 end) as aug,       
           sum(case when to_char(date_created,'mm') = '09' then 1 else 0 end) as sep,
           sum(case when to_char(date_created,'mm') = '10' then 1 else 0 end) as oct,
           sum(case when to_char(date_created,'mm') = '11' then 1 else 0 end) as nov,
           sum(case when to_char(date_created,'mm') = '12' then 1 else 0 end) as dec              
      from cnr_ticket
     where to_char(date_created,'yyyy')='2018' 
     group by subcategory

Rextester Demo

Thanks! this is near to the actual data that I need, the count is adding 1-4 rows, it's close so thanks! — Patrick Brian Garcia, Jan 15 '19 at 09:13

How to count number of rows returned per month in oracle?

3 Answers3