Forum Stats

  • 3,851,487 Users
  • 2,263,988 Discussions
  • 7,904,736 Comments

Discussions

Pivot on multiple variables

user13117585
user13117585 Member Posts: 662 Bronze Badge
edited Mar 30, 2017 4:58AM in SQL & PL/SQL

Hello again,

I'm sorry to bother you again with my stupid questions, but I was hoping someone can advise on a better approach.

Imagine I have the following table:

CREATE TABLE foo (  fid NUMBER(10),   ftype VARCHAR2(100),  created_on DATE);INSERT INTO foo(fid, ftype, created_on) VALUES(1, 'AA', SYSDATE);INSERT INTO foo(fid, ftype, created_on) VALUES(2, 'AB', SYSDATE);INSERT INTO foo(fid, ftype, created_on) VALUES(3, 'XX', SYSDATE);INSERT INTO foo(fid, ftype, created_on) VALUES(4, 'XY', SYSDATE);INSERT INTO foo(fid, ftype, created_on) VALUES(5, 'AA', SYSDATE - INTERVAL '1' YEAR);INSERT INTO foo(fid, ftype, created_on) VALUES(6, 'AB', SYSDATE - INTERVAL '1' YEAR);INSERT INTO foo(fid, ftype, created_on) VALUES(7, 'XX', SYSDATE - INTERVAL '1' YEAR);INSERT INTO foo(fid, ftype, created_on) VALUES(8, 'XY', SYSDATE - INTERVAL '1' YEAR);INSERT INTO foo(fid, ftype, created_on) VALUES(9, 'XZ', SYSDATE - INTERVAL '1' YEAR);

I would like to have for each year, a column that has the total amont of each type like the following query does... But the thing is that it is very tedious to write for all the months.

SELECT EXTRACT(YEAR FROM created_on) AS year,        /* jan */       COUNT(CASE WHEN ftype = 'AA' AND EXTRACT(MONTH FROM created_on) = 1 THEN 1 END) AS cnt_aa_jan,        COUNT(CASE WHEN ftype = 'AB' AND EXTRACT(MONTH FROM created_on) = 1 THEN 1 END) AS cnt_ab_jan,        COUNT(CASE WHEN ftype NOT IN ('AA', 'AB') AND EXTRACT(MONTH FROM created_on) = 1 THEN 1 END) AS cnt_other_jan,       COUNT(CASE WHEN EXTRACT(MONTH FROM created_on) = 1 THEN 1 END) AS cnt_all_jan,       /* feb */       COUNT(CASE WHEN ftype = 'AA' AND EXTRACT(MONTH FROM created_on) = 2 THEN 1 END) AS cnt_aa_feb,        COUNT(CASE WHEN ftype = 'AB' AND EXTRACT(MONTH FROM created_on) = 2 THEN 1 END) AS cnt_ab_feb,        COUNT(CASE WHEN ftype NOT IN ('AA', 'AB') AND EXTRACT(MONTH FROM created_on) = 2 THEN 1 END) AS cnt_other_feb,       COUNT(CASE WHEN EXTRACT(MONTH FROM created_on) = 2 THEN 1 END) AS cnt_all_feb,       /* mar */       COUNT(CASE WHEN ftype = 'AA' AND EXTRACT(MONTH FROM created_on) = 3 THEN 1 END) AS cnt_aa_mar,        COUNT(CASE WHEN ftype = 'AB' AND EXTRACT(MONTH FROM created_on) = 3 THEN 1 END) AS cnt_ab_mar,        COUNT(CASE WHEN ftype NOT IN ('AA', 'AB') AND EXTRACT(MONTH FROM created_on) = 3 THEN 1 END) AS cnt_other_mar,       COUNT(CASE WHEN EXTRACT(MONTH FROM created_on) = 3 THEN 1 END) AS cnt_all_mar  FROM foo GROUP BY EXTRACT(YEAR FROM created_on);

Do you think that it is possible to make this query simpler?

Thank you,

Tagged:
BluShadow

Best Answer

  • Stew Ashton
    Stew Ashton Member Posts: 2,911 Bronze Crown
    edited Mar 30, 2017 4:37AM Answer ✓

    You may want 0 instead of NULL in the empty columns, in which case:

    with all_months as (  select * from (    select distinct EXTRACT(YEAR FROM created_on) AS yr    from foo  ),(    select level mon,    0 cnt_aa,    0 cnt_ab,    0 cnt_other,    0 cnt_all    from dual connect by level <= 12  )), foo_months as (  select EXTRACT(YEAR FROM created_on) AS yr,  extract(month from created_on) AS mon,  case when ftype = 'AA' then 1 end cnt_aa,  case when ftype = 'AB' then 1 end cnt_ab,  CASE WHEN ftype NOT IN ('AA', 'AB') THEN 1 END cnt_other,  1 cnt_all  from foo)select * from (  select * from foo_months  union all  select * from all_months)pivot(   sum(cnt_aa)cnt_aa, sum(cnt_ab)cnt_ab, sum(cnt_other)cnt_other, sum(cnt_all)cnt_all  for mon in(1 jan,2 feb,3 mar));

    YRJAN_CNT_AAJAN_CNT_ABJAN_CNT_OTHERJAN_CNT_ALLFEB_CNT_AAFEB_CNT_ABFEB_CNT_OTHERFEB_CNT_ALLMAR_CNT_AAMAR_CNT_ABMAR_CNT_OTHERMAR_CNT_ALL
    2017000000001124
    2016000000001135

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 42,305 Red Diamond
    edited Mar 30, 2017 4:19AM

    What database version are you on?

    From 11g, you could use the PIVOT keyword, or there may be some way of using MATCH_RECOGNIZE in 12c (but I don't have 12c database to hand at the moment), but still you're going to need to specify all the columns in one way or another.

  • Stew Ashton
    Stew Ashton Member Posts: 2,911 Bronze Crown
    edited Mar 30, 2017 4:23AM
    select * from (  select EXTRACT(YEAR FROM created_on) AS year,  extract(month from created_on) AS mon,  case when ftype = 'AA' then 1 end cnt_aa,  case when ftype = 'AB' then 1 end cnt_ab,  CASE WHEN ftype NOT IN ('AA', 'AB') THEN 1 END cnt_other,  1 cnt_all  from foo)pivot(   sum(cnt_aa)cnt_aa, sum(cnt_ab)cnt_ab, sum(cnt_other)cnt_other, sum(cnt_all)cnt_all  for mon in(1 jan,2 feb,3 mar));

    YEARJAN_CNT_AAJAN_CNT_ABJAN_CNT_OTHERJAN_CNT_ALLFEB_CNT_AAFEB_CNT_ABFEB_CNT_OTHERFEB_CNT_ALLMAR_CNT_AAMAR_CNT_ABMAR_CNT_OTHERMAR_CNT_ALL
    2017 1124
    2016 1135
    BluShadowuser13117585
  • BluShadow
    BluShadow Member, Moderator Posts: 42,305 Red Diamond
    edited Mar 30, 2017 4:30AM

    Or the old fashioned way, factor out the counts to a subquery, and then pivot using the case statements, still lengthy, but not as messy...

    (untested so excuse any typos)

    WITH c (yr, mth, cnt_aa, cnt_ab, cnt_other, cnt_all) as (        SELECT EXTRACT(YEAR FROM created_on)              ,EXTRACT(MONTH FROM created_on)              ,COUNT(CASE WHEN ftype = 'AA' THEN 1 END)              ,COUNT(CASE WHEN ftype = 'AB' THEN 1 END)              ,COUNT(CASE WHEN ftype NOT IN ('AA', 'AB') THEN 1 END)              ,COUNT(*)        FROM  foo         GROUP BY EXTRACT(YEAR FROM created_on)                ,EXTRACT(MONTH FROM created_on)      )select year      ,max(case when mth=1 then cnt_aa) as cnt_aa_jan      ,max(case when mth=1 then cnt_ab) as cnt_ab_jan      ,max(case when mth=1 then cnt_other) as cnt_other_jan      ,max(case when mth=1 then cnt_ab) as cnt_all_jan      ,max(case when mth=2 then cnt_aa) as cnt_aa_feb      ,max(case when mth=2 then cnt_ab) as cnt_ab_feb      ,max(case when mth=2 then cnt_other) as cnt_other_feb      ,max(case when mth=2 then cnt_ab) as cnt_all_feb      ,max(case when mth=3 then cnt_aa) as cnt_aa_mar      ,max(case when mth=3 then cnt_ab) as cnt_ab_mar      ,max(case when mth=3 then cnt_other) as cnt_other_mar      ,max(case when mth=3 then cnt_ab) as cnt_all_mar      ,max(case when mth=4 then cnt_aa) as cnt_aa_apr      ,max(case when mth=4 then cnt_ab) as cnt_ab_apr      ,max(case when mth=4 then cnt_other) as cnt_other_apr      ,max(case when mth=4 then cnt_ab) as cnt_all_apr      ,max(case when mth=5 then cnt_aa) as cnt_aa_may      ,max(case when mth=5 then cnt_ab) as cnt_ab_may      ,max(case when mth=5 then cnt_other) as cnt_other_may      ,max(case when mth=5 then cnt_ab) as cnt_all_may      ,max(case when mth=6 then cnt_aa) as cnt_aa_jun      ,max(case when mth=6 then cnt_ab) as cnt_ab_jun      ,max(case when mth=6 then cnt_other) as cnt_other_jun      ,max(case when mth=6 then cnt_ab) as cnt_all_jun      ,max(case when mth=7 then cnt_aa) as cnt_aa_jul      ,max(case when mth=7 then cnt_ab) as cnt_ab_jul      ,max(case when mth=7 then cnt_other) as cnt_other_jul      ,max(case when mth=7 then cnt_ab) as cnt_all_jul      ,max(case when mth=8 then cnt_aa) as cnt_aa_aug      ,max(case when mth=8 then cnt_ab) as cnt_ab_aug      ,max(case when mth=8 then cnt_other) as cnt_other_aug      ,max(case when mth=8 then cnt_ab) as cnt_all_aug      ,max(case when mth=9 then cnt_aa) as cnt_aa_sep      ,max(case when mth=9 then cnt_ab) as cnt_ab_sep      ,max(case when mth=9 then cnt_other) as cnt_other_sep      ,max(case when mth=9 then cnt_ab) as cnt_all_sep      ,max(case when mth=10 then cnt_aa) as cnt_aa_oct      ,max(case when mth=10 then cnt_ab) as cnt_ab_oct      ,max(case when mth=10 then cnt_other) as cnt_other_oct      ,max(case when mth=10 then cnt_ab) as cnt_all_oct      ,max(case when mth=11 then cnt_aa) as cnt_aa_nov      ,max(case when mth=11 then cnt_ab) as cnt_ab_nov      ,max(case when mth=11 then cnt_other) as cnt_other_nov      ,max(case when mth=11 then cnt_ab) as cnt_all_nov      ,max(case when mth=12 then cnt_aa) as cnt_aa_dec      ,max(case when mth=12 then cnt_ab) as cnt_ab_dec      ,max(case when mth=12 then cnt_other) as cnt_other_dec      ,max(case when mth=12 then cnt_ab) as cnt_all_decfrom  cgroup by yr
    user13117585
  • Stew Ashton
    Stew Ashton Member Posts: 2,911 Bronze Crown
    edited Mar 30, 2017 4:37AM Answer ✓

    You may want 0 instead of NULL in the empty columns, in which case:

    with all_months as (  select * from (    select distinct EXTRACT(YEAR FROM created_on) AS yr    from foo  ),(    select level mon,    0 cnt_aa,    0 cnt_ab,    0 cnt_other,    0 cnt_all    from dual connect by level <= 12  )), foo_months as (  select EXTRACT(YEAR FROM created_on) AS yr,  extract(month from created_on) AS mon,  case when ftype = 'AA' then 1 end cnt_aa,  case when ftype = 'AB' then 1 end cnt_ab,  CASE WHEN ftype NOT IN ('AA', 'AB') THEN 1 END cnt_other,  1 cnt_all  from foo)select * from (  select * from foo_months  union all  select * from all_months)pivot(   sum(cnt_aa)cnt_aa, sum(cnt_ab)cnt_ab, sum(cnt_other)cnt_other, sum(cnt_all)cnt_all  for mon in(1 jan,2 feb,3 mar));

    YRJAN_CNT_AAJAN_CNT_ABJAN_CNT_OTHERJAN_CNT_ALLFEB_CNT_AAFEB_CNT_ABFEB_CNT_OTHERFEB_CNT_ALLMAR_CNT_AAMAR_CNT_ABMAR_CNT_OTHERMAR_CNT_ALL
    2017000000001124
    2016000000001135
  • user13117585
    user13117585 Member Posts: 662 Bronze Badge
    edited Mar 30, 2017 4:58AM

    Thank you all of you. I see that my query was not the best and you gave me good ideas to improve. The solution BluShadow suggested is easy to put 0. Thank you

This discussion has been closed.