Gollathor Gollathor - 2 months ago 7
SQL Question

Trying to count the number of distinct values within my defined categories in SQL Server

I am trying to get the number of distinct Business ID's that fall into each of the categories that I defined in a sub query. I am wondering if this is possible, and if so, how would I go about doing it.

So far this code counts the number of businesses and then returns units sold. What I want is for it to count the number of businesses that fall into each of the 4 categories defined by the case when statement.

Here is my query so far.....

SELECT
COUNT(desk.BusinessID) AS '#of Partners',
'Units Sold'
FROM
(SELECT
BusinessID,
"Units Sold" = CASE
WHEN SUM(QA.ActivityQuantity) = 0 THEN '0 Units'
WHEN SUM(QA.ActivityQuantity) < 25
AND SUM(QA.ActivityQuantity) > 0 THEN '1-25 Units'
WHEN SUM(QA.ActivityQuantity) < 50
AND SUM(QA.ActivityQuantity) > 25 THEN '25-50 Units'
WHEN SUM(QA.ActivityQuantity) > 50 THEN '> 50'
ELSE 'NULL'
END
FROM
Points.QualifyingActivity QA
JOIN
Points.AwardRule AR ON AR.AwardRuleID = QA.AwardRuleID
JOIN
Dimension.DimMembers P ON P.BusinessID = QA.BusinessID
WHERE
P.CountryCode = 'US' or P.CountryCode ='RU'
AND AR.AwardRuleDescription LIKE '%Desktop%') desk ;


Any help is greatly appreciated!

Answer

You would need to group by that Units Sold case statement and reference that field:

SELECT COUNT(desk.BusinessID) AS '#of Partners', desk.[Units Sold]
FROM(
    SELECT BusinessID, 
        CASE WHEN SUM(QA.ActivityQuantity) = 0 THEN '0 Units'
            WHEN SUM(QA.ActivityQuantity) <25 AND SUM(QA.ActivityQuantity) >0 THEN '1-25 Units'
            WHEN SUM(QA.ActivityQuantity) <50 AND SUM(QA.ActivityQuantity) >25 THEN '25-50 Units'
            WHEN SUM(QA.ActivityQuantity) >50 THEN '>50'
            ELSE 'NULL'
            END as [Units Sold]
        FROM Points.QualifyingActivity QA
            JOIN Points.AwardRule AR
                ON AR.AwardRuleID = QA.AwardRuleID
            JOIN Dimension.DimMembers P
                ON P.BusinessID = QA.BusinessID
WHERE P.CountryCode = 'US' or P.CountryCode ='RU'
    AND AR.AwardRuleDescription LIKE '%Desktop%') desk
group by Desk.[Units Sold]