Search code examples
sqldatabasedb2aggregation

Count Distinct IDs against type and Date in SQL

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

Table

So for above data output would be:

Output Required

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:

enter image description here

Solution

  • 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 ;