CODEWITHSUNDEEP
sqloracle-databasepivot
Ning
Ning

Reputation: 595

SQL get total number and using pivot at the same time

I got two tables with the relationship like this

enter image description here

I have a data set like this

enter image description here

and want to get ALL the demerits, the code, description, total number of offences committed for the demerit code so far in any month (of any year) and then the total of offences committed for the demerit code in each month (of any year) which is basically a result like this

enter image description here

I can get the total number(by using group by) and monthly data(by using pivot rows as columns) separately, but don't know how to get them in one query. Any help would be appreciated.

 select * from (
 select 
        d.dem_code, 
        d.dem_code as dem_code_copy,
        d.dem_description, 
        case 
        when EXTRACT(month FROM off_datetime) = 1 then 'Jan'
        when EXTRACT(month FROM off_datetime) = 2 then 'Feb'
        when EXTRACT(month FROM off_datetime) = 3 then 'Mar'
        when EXTRACT(month FROM off_datetime) = 4 then 'Apr'
        when EXTRACT(month FROM off_datetime) = 5 then 'May'
        when EXTRACT(month FROM off_datetime) = 6 then 'Jun'
        when EXTRACT(month FROM off_datetime) = 7 then 'Jul'
        when EXTRACT(month FROM off_datetime) = 8 then 'Aug'
        when EXTRACT(month FROM off_datetime) = 9 then 'Sep'
        when EXTRACT(month FROM off_datetime) = 10 then 'Oct'
        when EXTRACT(month FROM off_datetime) = 11 then 'Nov'
        when EXTRACT(month FROM off_datetime) = 12 then 'Dec'
        end
        as "Month"    
    from demerit d
    left join offence o on d.dem_code = o.dem_code
    order by d.dem_code 
    )
    pivot(
    count(dem_code_copy)
    for "Month"
    in (
        'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'
    )
)

This query gives me the monthly result

enter image description here


    select 
        d.dem_code, 
        d.dem_description,
        count(o.off_no) as total
    from demerit d
    left join offence o on d.dem_code = o.dem_code
    group by d.dem_code, d.dem_description
    order by d.dem_code

This query gives the total number

enter image description here

Upvotes: 3

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I would just use conditional aggregation:

select d.dem_code, d.dem_description, 
       sum(case when EXTRACT(month FROM off_datetime) = 1 then 1 else 0 end) as Jan,
       sum(case when EXTRACT(month FROM off_datetime) = 2 then 1 else 0 end) as Feb
       sum(case when EXTRACT(month FROM off_datetime) = 3 then 1 else 0 end) as Mar,
       sum(case when EXTRACT(month FROM off_datetime) = 4 then 1 else 0 end) as Apr,
       sum(case when EXTRACT(month FROM off_datetime) = 5 then 1 else 0 end) as May,
       sum(case when EXTRACT(month FROM off_datetime) = 6 then 1 else 0 end) as Jun,
       sum(case when EXTRACT(month FROM off_datetime) = 7 then 1 else 0 end) as Jul,
       sum(case when EXTRACT(month FROM off_datetime) = 8 then 1 else 0 end) as Aug,
       sum(case when EXTRACT(month FROM off_datetime) = 9 then 1 else 0 end) as Sep,
       sum(case when EXTRACT(month FROM off_datetime) = 10 then 1 else 0 end) as Oct,
       sum(case when EXTRACT(month FROM off_datetime) = 11 then 1 else 0 end) as Nov,
       sum(case when EXTRACT(month FROM off_datetime) = 12 then 1 else 0 end) as Dec
from demerit d left join
     offence o
     on d.dem_code = o.dem_code
group by d.dem_code, d.dem_description;

Upvotes: 2

Related Questions