Case when with distinct value

April 5, 2024

I need to calculate how many distinct customers have visited in the tp.places per month and i’ve tried three different solutions with no luck. What am i missing out?

SELECT
    DISTINCT tp.place,
    tp.brand,
    SUM (CASE WHEN kr.calendar_key BETWEEN '2024-01-01' AND '2024-01-31' THEN 1 END) as 1st,
    SUM (CASE WHEN kr.calendar_key BETWEEN '2024-02-01' AND '2024-02-29' THEN DISTINCT kr.customer_key END) as 2nd
    SUM (CASE WHEN kr.calendar_key BETWEEN '2024-03-01' AND '2024-03-31' THEN COUNT(DISTINCT kr.customer_key) END) as 3rd,
FROM
    orders.f_receipts kr
    INNER JOIN dim.d_place_of_business tp ON tp.business_key = kr.business_key
WHERE
    ...

I’ve tried three different solution and hoped something like this:

Place   Jan   Feb   March
nr1     1150  900   1300
nr2     800   990   700
etc.

Edited the code to look more readable. Also the desired output.

>Solution :

Don’t use SUM, use COUNT(DISTINCT) when you want to count distinct customers.

SELECT
    tp.place,
    tp.brand,
    COUNT (DISTINCT CASE WHEN kr.calendar_key BETWEEN '2024-01-01' AND '2024-01-31' THEN kr.customer_key END) as january,
    COUNT (DISTINCT CASE WHEN kr.calendar_key BETWEEN '2024-02-01' AND '2024-02-29' THEN kr.customer_key END) as february,
    COUNT (DISTINCT CASE WHEN kr.calendar_key BETWEEN '2024-03-01' AND '2024-03-31' THEN kr.customer_key END) as march,
    ...
FROM
    orders.f_receipts kr
    INNER JOIN dim.d_place_of_business tp ON tp.business_key = kr.business_key
WHERE
    ...
GROUP BY tp.place, tp.brand
ORDER BY tp.place, tp.brand;