RedTshirt RedTshirt - 1 month ago 7
SQL Question

SQL group by case statement with COUNT DISTINCT

My table in SQL Server 2012 looks like this:

SaleDate CustomerID Customer
2014-03-01 NULL George
2014-03-01 NULL John
2014-03-01 125 Emy
2014-04-01 126 Natasha
2014-04-01 127 NULL
2014-05-01 128 Jack


I want to find out the number of unique customers per sale date, considering that:


  • there are cases when the CustomerID is missing (but the Customer name is known)

  • and there are cases when CustomerID exists (but the Customer name is missing)



The desired output is:

SaleDate UniqueCustomers
2014-03-01 3
2014-04-01 2
2014-05-01 1


I tried this query, but - of course - I get the following error: Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause:

SELECT SaleDate,
CASE
WHEN Customer IS NULL THEN COUNT(DISTINCT CustomerID)
ELSE COUNT(DISTINCT Customer)
END AS "UniqueCustomers"
FROM TableA
GROUP BY SaleDate,
CASE
WHEN Customer IS NULL THEN COUNT(DISTINCT CustomerID)
ELSE COUNT(DISTINCT Customer)
END


There is not a possibility to UPDATE the table to get rid of the NULL values.

Answer

That's not the right way to do it. Use Count outside of case statement.

Considering that CustomerID is a Integer Column. Try this

SELECT SaleDate,
       Count(DISTINCT CASE
                        WHEN Customer IS NULL THEN CONVERT(VARCHAR(50), CustomerID)
                        ELSE Customer
                      END) AS UniqueCustomers
FROM   Yourtable
GROUP  BY SaleDate