SQL Server: Sum of Unique values for multiple columns

Question

Using SQL Server, I have a table as shown in the sample table below. I need to have sum of all the unique values per the columns "BookOrder, StationaryOrder, and Printing Order".

SAMPLE TABLE:

KeyIDCustomer   BooksOrder  StationaryOrder PrintingOrder
29945843         1070756    1891514            198876
29945843         1070756    1893827            198876
29945843         1070758    1891514            198876
29945843         1070758    1893827            198876

I am using the below coding to achieve this goal.

Select DISTINCT KeyIDCustomerID,
Sum(Case when BooksOrder is not null then 1 else 0 End) TotalBookOrders,  
Sum(Case when StationaryOrder is not null then 1 else 0 End) TotalStationaryOrder,
Sum(Case when PrintingOrder is not null then 1 else 0 End)TotalPrintingOrder

With this coding in am getting the results as below

  KeyIDCustomerID   TotalBookOrders TotalStationaryOrder    TotalPrintingOrder
   29945843                    4                       4                    4

I expect the results to be like this

  KeyIDCustomerID   TotalBookOrders TotalStationaryOrder    TotalPrintingOrder
   29945843                    2                       2                    1

Is there a way i can accomplish this Goal in SQL?

Thanks

Answer 1

i think the proper term for

sum of all the unique values per the columns

is "count of unique values"

COUNT (DISTINCT column_name) returns the number of unique, non-null values in column_name

Select 
KeyIDCustomerID,
COUNT(DISTINCT BooksOrder) as TotalBookOrders,  
COUNT(DISTINCT StationaryOrder) as TotalStationaryOrder,
COUNT(DISTINCT PrintingOrder) as TotalPrintingOrder
FROM SAMPLE_TABLE
GROUP BY KeyIDCustomerID