Gollathor - 10 months ago 36

SQL Question

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 Source

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]
```