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,