1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62
|
SQL>
SQL> CREATE TABLE dt (d DATE, t CHAR(1));
Table créée.
SQL>
SQL> INSERT INTO dt VALUES ('10/12/06', 'A');
1 ligne créée.
SQL> INSERT INTO dt VALUES ('10/12/06', 'A');
1 ligne créée.
SQL> INSERT INTO dt VALUES ('10/12/06', 'C');
1 ligne créée.
SQL> INSERT INTO dt VALUES ('11/12/06', 'B');
1 ligne créée.
SQL> INSERT INTO dt VALUES ('11/12/06', 'A');
1 ligne créée.
SQL> INSERT INTO dt VALUES ('12/12/06', 'B');
1 ligne créée.
SQL>
SQL> COMMIT;
Validation effectuée.
SQL>
SQL> SELECT d As "DATE", SUM(count_A) AS "Type A", SUM(count_B) AS "Type B", SUM
(count_C) AS "Type C"
2 FROM
3 (
4 SELECT DISTINCT d, count_A, 0 AS count_B, 0 AS count_C FROM (
5 SELECT d, COUNT(t) OVER (PARTITION BY d) count_A FROM (
6 SELECT * FROM dt WHERE t ='A'))
7 UNION
8 SELECT DISTINCT d, 0 AS count_A, count_B, 0 AS count_C FROM (
9 SELECT d, COUNT(t) OVER (PARTITION BY d) count_B FROM (
10 SELECT * FROM dt WHERE t ='B'))
11 UNION
12 SELECT DISTINCT d, 0 AS count_A, 0 AS count_B, count_C FROM (
13 SELECT d, COUNT(t) OVER (PARTITION BY d) count_C FROM (
14 SELECT * FROM dt WHERE t ='C'))
15 )
16 GROUP BY d ORDER BY d;
DATE Type A Type B Type C
-------- ---------- ---------- ----------
10/12/06 2 0 1
11/12/06 1 1 0
12/12/06 0 1 0
SQL> |
Partager