Hello all,
I have another question about SQL statement. Imagine the following scenario.
drop table properties;
CREATE TABLE properties
(
code varchar2(100)
);
insert into properties values('foo');
insert into properties values('bar');
commit;
drop table all_engines;
CREATE TABLE all_engines
(
id number(10) -- pk
);
insert into all_engines values(1);
insert into all_engines values(2);
insert into all_engines values(3);
insert into all_engines values(4);
insert into all_engines values(5);
drop table engines;
CREATE TABLE engines
(
id NUMBER(10), -- FK to all_engines
code varchar2(100), -- fk to properties
regio varchar2(100), -- only 3 values are possible (asia, europe, africa)
price NUMBER(10)
);
INSERT INTO engines(id, code, regio, price) VALUES(1, 'foo', 'africa', 2);
INSERT INTO engines(id, code, regio, price) VALUES(1, 'foo', 'europe', 2);
INSERT INTO engines(id, code, regio, price) VALUES(2, 'foo', 'africa', 2);
INSERT INTO engines(id, code, regio, price) VALUES(3, 'bar', 'europe', 2);
I have a table with some properties. It is only string values. Nothing very special.
I have another table with all possible values. All ids that should be counted.
And the last table is a sort of key value hashmap.
I would like to count, for each regio or group of regions how many rows are present by code.
The following query does more or less what I need.
SELECT code,
COUNT(CASE WHEN africa IS NOT NULL AND europe IS NULL AND asia is NULL THEN 1 END) AS africa,
COUNT(CASE WHEN africa IS NULL AND europe IS NOT NULL AND asia is NULL THEN 1 END) AS europe,
COUNT(CASE WHEN africa IS NULL AND europe IS NULL AND asia is not NULL THEN 1 END) AS asia,
COUNT(CASE WHEN africa IS NOT NULL AND europe IS NOT NULL AND asia is NULL THEN 1 END) AS africa_europe,
COUNT(CASE WHEN africa IS NOT NULL AND europe IS NULL AND asia is not NULL THEN 1 END) AS africa_asia,
COUNT(CASE WHEN africa IS NULL AND europe IS NOT NULL AND asia is NOT NULL THEN 1 END) AS europe_asia,
COUNT(CASE WHEN africa IS NOT NULL AND europe IS NOT NULL AND asia is NOT NULL THEN 1 END) AS africa_europe_asia,
COUNT(CASE WHEN africa IS NULL AND europe IS NULL AND asia IS NULL THEN 1 END) AS none
FROM (
SELECT a.id,
e.code,
MIN(CASE WHEN regio = 'africa' THEN 1 END) AS africa,
MIN(CASE WHEN regio = 'asia' THEN 1 END) AS asia,
MIN(CASE WHEN regio = 'europe' THEN 1 END) AS europe
FROM all_engines a LEFT JOIN engines e ON a.id = e.id
GROUP BY a.id, e.code
)
GROUP BY code
ORDER BY code
It returns this:
CODE AFRICA EUROPE ASIA AFRICA_EUROPE AFRICA_ASIA EUROPE_ASIA AFRICA_EUROPE_ASIA NONE
------- ---------- ---------- ---------- ------------- ----------- ----------- ------------------ ----------
bar 0 1 0 0 0 0 0 0
foo 1 0 0 1 0 0 0 0
0 0 0 0 0 0 0 2
However, I would like to have something like this:
CODE AFRICA EUROPE ASIA AFRICA_EUROPE AFRICA_ASIA EUROPE_ASIA AFRICA_EUROPE_ASIA NONE
------- ---------- ---------- ---------- ------------- ----------- ----------- ------------------ ----------
bar 0 1 0 0 0 0 0 4
foo 1 0 0 1 0 0 0 3
For each code, I count rows. If one, two or three regio appears for that code, I count it once. But all the other ids that don't have my regio, I need to count them in the last column. I hope that it is clear :D