Count Distinct IDs against type and Date in SQL-CodePudding

I have table where data looks like this. I am finding the total count of each TYPE against DATE.

So for above data output would be:

I tried following query but result contain duplicates counted more than once.

SELECT 
    DATE,
    SUM(CASE WHEN TYPE = 'Existing' THEN 1 ELSE 0 END) AS "EXISTING",
    SUM(CASE WHEN TYPE = 'New' THEN 1 ELSE 0 END) AS "NEW"
FROM 
    mytab
WHERE 
    DATE >= '2022-01-01'
GROUP BY    
    DATE ;

Output of Above query:

CodePudding user response：

Untested, but perhaps what you want:

SELECT 
    DATE,
    COUNT(DISTINCT CASE WHEN TYPE = 'Existing' THEN ID END) AS "EXISTING",
    COUNT(DISTINCT CASE WHEN TYPE = 'New' THEN ID END) AS "NEW"
FROM 
    mytab
WHERE 
    DATE >= '2022-01-01'
GROUP BY    
    DATE ;

CodePudding user response：

One approach would be to do conditional aggregation on top of a subquery which first takes only distinct records from your original table.

SELECT 
    DATE,
    COUNT(CASE WHEN TYPE = 'Existing' THEN 1 END) AS "EXISTING",
    COUNT(CASE WHEN TYPE = 'New' THEN 1 END) AS "NEW"
FROM (SELECT DISTINCT ID, TYPE, DATE FROM mytab) t
WHERE DATE >= '2022-01-01'
GROUP BY DATE;