MS SQL Server - DISTINCT CustomerIDs not selected when using CASE for one of columns-CodePudding

I'm trying to select records of customers to see purchases by year.

SELECT DISTINCT(c.[ScvId]),
    EmailAddress,
    CASE 
        WHEN c.ScvId = T.ScvId AND ProductId = '2019' 
            THEN 1 
            ELSE 0 
    END AS FinalTicketPurchaser_2019,
    CASE 
        WHEN c.ScvId = T.ScvId AND ProductId = '2018' 
            THEN 1 
            ELSE 0 
    END AS FinalTicketPurchaser_2018
FROM
    Customer c
LEFT JOIN 
    Transaction T ON c.ScvId = T.ScvId
ORDER BY
    c.scvid

This query returns duplicates of ScvId:

ScvId   FinalTicketPurchaser_2019   FinalTicketPurchaser_2018
-------------------------------------------------------------
 38                 0                           0
 39                 0                           0
 39                 0                           1
 40                 0                           0

What is the best way to correct this query?

CodePudding user response：

You need to aggregate by the ScvId, and then take the count of the CASE expressions:

SELECT
    c.ScvId,
    EmailAddress,
    COUNT(CASE WHEN ProductId = '2019' THEN 1 END) AS FinalTicketPurchaser_2019,
    COUNT(CASE WHEN ProductId = '2018' THEN 1 END) AS FinalTicketPurchaser_2018
FROM Customer c
LEFT JOIN Transaction T ON c.ScvId = T.ScvId
GROUP BY
    c.ScvId,
    EmailAddress
ORDER BY
    c.ScvId;

CodePudding user response：

You should use Group By and SUM instead of distinct. Also, if there's a chance that a given SvcId can have multiple email addresses, then you would need to leave email address out of the query. Otherwise, you could get duplicate SvcIds.

SELECT c.[ScvId]
    ,SUM(CASE WHEN c.ScvId = T.ScvId AND ProductId = '2019' THEN 1 ELSE 0 END) AS FinalTicketPurchaser_2019
    ,SUM(CASE WHEN c.ScvId = T.ScvId AND ProductId = '2018' THEN 1 ELSE 0 END) AS FinalTicketPurchaser_2018
FROM Customer c
LEFT JOIN Transaction T ON c.ScvId = T.ScvId
GROUP BY c.[SvcId]
order by c.scvid