Table PJDetails

PROJECT    Date         PJCode    PJCount
Google     01/22/2018   0100        4
Google     02/12/2018   0200        3
Google     05/12/2018   0201        2
Google     06/12/2018   0202        2
Google     03/28/2019   0300        6
Google     03/28/2019   0200        2
Google     03/28/2019   0100        3
Google     07/28/2019   0101        3
MSFT       02/15/2017   0201        2
MSFT       02/15/2017   0200        1
MSFT       04/15/2017   0202        1
MSFT       02/15/2017   0300        4
MSFT       04/18/2019   0300        5
MSFT       05/19/2019   0202        6
MSFT       09/19/2019   0100        3
MSFT       06/19/2019   0100        3

Table EXPLAN

PROJECT    CODE    Type
Google     0100    Win
Google     0101    Win
Google     0200    Lose
Google     0201    Lose
Google     0202    Lose
Google     0300    Fair
MSFT       0100    Win
MSFT       0101    Win
MSFT       0200    Lose
MSFT       0201    Lose
MSFT       0202    Lose
MSFT       0300    Fair

I have to extract the result of total WINCOUNT and LOSECOUNT for each Project by years. But there are multiple PJCode can represent WIN and LOSE. Below is my expected result.

Expexted result

Project    Year    WINCOUNT    LOSECOUNT
GOOGLE     2018       4            7
GOOGLE     2019       6            2
MSFT       2017       0            4
MSFT       2019       6            6

Here is the code I currently have. I can only extract the data and count the total and I've no idea about how to count the total WINCOUNT and LOSECOUNT for each Project by years.

I just know how to find it one by one just like below.

SELECT
    Project, Date, SUM(PJCount) as LOSECOUNT
    From PJDetails
    Where PJCode = '0200' or PJCode = '0201' or PJCode = '0202'

Thanks for your help.

CodePudding user response：

Join the tables and then use conditional aggregation:

SELECT p.project,
       EXTRACT(YEAR FROM p."DATE") AS year, 
       COALESCE(SUM(CASE e.Type WHEN 'Win'  THEN p.pjcount END), 0) AS wincount, 
       COALESCE(SUM(CASE e.Type WHEN 'Lose' THEN p.pjcount END), 0) AS losecount
FROM   PJDetails p
       INNER JOIN Explan e
       ON (e.project = p.project AND p.pjcode = e.code)
GROUP BY p.Project, EXTRACT(YEAR FROM p."DATE");

Which, for the sample data:

CREATE TABLE PJDetails (PROJECT, "DATE", PJCode, PJCount) AS
SELECT 'Google', DATE '2018-01-22', '0100', 4 FROM DUAL UNION  ALL
SELECT 'Google', DATE '2018-02-12', '0200', 3 FROM DUAL UNION  ALL
SELECT 'Google', DATE '2018-05-12', '0201', 2 FROM DUAL UNION  ALL
SELECT 'Google', DATE '2018-06-12', '0202', 2 FROM DUAL UNION  ALL
SELECT 'Google', DATE '2019-03-28', '0300', 6 FROM DUAL UNION  ALL
SELECT 'Google', DATE '2019-03-28', '0200', 2 FROM DUAL UNION  ALL
SELECT 'Google', DATE '2019-03-28', '0100', 3 FROM DUAL UNION  ALL
SELECT 'Google', DATE '2019-07-28', '0101', 3 FROM DUAL UNION  ALL
SELECT 'MSFT',   DATE '2017-02-15', '0201', 2 FROM DUAL UNION  ALL
SELECT 'MSFT',   DATE '2017-02-15', '0200', 1 FROM DUAL UNION  ALL
SELECT 'MSFT',   DATE '2017-04-15', '0202', 1 FROM DUAL UNION  ALL
SELECT 'MSFT',   DATE '2017-02-15', '0300', 4 FROM DUAL UNION  ALL
SELECT 'MSFT',   DATE '2019-04-18', '0300', 5 FROM DUAL UNION  ALL
SELECT 'MSFT',   DATE '2019-05-19', '0202', 6 FROM DUAL UNION  ALL
SELECT 'MSFT',   DATE '2019-09-19', '0100', 3 FROM DUAL UNION  ALL
SELECT 'MSFT',   DATE '2019-06-19', '0100', 3 FROM DUAL;

CREATE TABLE EXPLAN (PROJECT, CODE, Type) AS
SELECT 'Google', '0100', 'Win'  FROM DUAL UNION ALL
SELECT 'Google', '0101', 'Win'  FROM DUAL UNION ALL
SELECT 'Google', '0200', 'Lose' FROM DUAL UNION ALL
SELECT 'Google', '0201', 'Lose' FROM DUAL UNION ALL
SELECT 'Google', '0202', 'Lose' FROM DUAL UNION ALL
SELECT 'Google', '0300', 'Fair' FROM DUAL UNION ALL
SELECT 'MSFT',   '0100', 'Win'  FROM DUAL UNION ALL
SELECT 'MSFT',   '0101', 'Win'  FROM DUAL UNION ALL
SELECT 'MSFT',   '0200', 'Lose' FROM DUAL UNION ALL
SELECT 'MSFT',   '0201', 'Lose' FROM DUAL UNION ALL
SELECT 'MSFT',   '0202', 'Lose' FROM DUAL UNION ALL
SELECT 'MSFT',   '0300', 'Fair' FROM DUAL;

Outputs:

PROJECT	YEAR	WINCOUNT	LOSECOUNT
Google	2018	4	7
Google	2019	6	2
MSFT	2017	0	4
MSFT	2019	6	6

db<>fiddle here