Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Pivot on multiple variables

user13117585Mar 30 2017 — edited Mar 30 2017

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,

This post has been answered by Stew Ashton on Mar 30 2017
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 27 2017
Added on Mar 30 2017
5 comments
371 views