March 22, 2016 at 10:45 am
I need to do a report for all available flavors sold in different regions. However I only want to count chocolate-type flavors once. So if I region sold 3 different chocolate flavors, the count would still only be 1.
Here is some sample data:
SELECT 'NW' AS Region,'chocolate Swirl' As Flavor, 1 AS isAvailable
UNION ALL SELECT 'NW','chocolate',1
UNION ALL SELECT 'NW','vanilla',1
UNION ALL SELECT 'NW','vanilla bean',0
UNION ALL SELECT 'NW','french vanilla',1
UNION ALL SELECT 'SW','chocolate',1
UNION ALL SELECT 'SW','chocolate chip',1
UNION ALL SELECT 'SW','chocolate swirl',1
UNION ALL SELECT 'SW','mint chocolate chip',0
UNION ALL SELECT 'NE','chocolate swirl',1
UNION ALL SELECT 'NE','vanilla',1
UNION ALL SELECT 'NE','vanilla bean',0
UNION ALL SELECT 'NE','chocolate chip',1
UNION ALL SELECT 'NE','strawberry',1
UNION ALL SELECT 'NE','butterscotch',0;
--only count all chocolate flavors once, so if a region has a chocolate count greater than 1, then it should always be 1. If it's 0, then it's 0.
Here is a sample report:
--NW available flavor count = 3
--SW available flavor count = 1
--NE available flavor count = 3
I tried using a count a partition like this:
select count(isAvailable) OVER(partition by region) AS flavorAvailabilityCount
from IceCreamSales
but the totals were all wrong.
Is there a way to setup some type of conditional count?
Thanks!
March 22, 2016 at 10:53 am
This might help. If you want your results to be grouped, you don't use the OVER clause, you need to use GROUP BY.
SELECT 'NW' AS Region,'chocolate Swirl' As Flavor, 1 AS isAvailable INTO #Flavors
UNION ALL SELECT 'NW','chocolate',1
UNION ALL SELECT 'NW','vanilla',1
UNION ALL SELECT 'NW','vanilla bean',0
UNION ALL SELECT 'NW','french vanilla',1
UNION ALL SELECT 'SW','chocolate',1
UNION ALL SELECT 'SW','chocolate chip',1
UNION ALL SELECT 'SW','chocolate swirl',1
UNION ALL SELECT 'SW','mint chocolate chip',0
UNION ALL SELECT 'NE','chocolate swirl',1
UNION ALL SELECT 'NE','vanilla',1
UNION ALL SELECT 'NE','vanilla bean',0
UNION ALL SELECT 'NE','chocolate chip',1
UNION ALL SELECT 'NE','strawberry',1
UNION ALL SELECT 'NE','butterscotch',0;
SELECT Region,
COUNT( DISTINCT CASE WHEN Flavor LIKE 'chocolate%' THEN 'chocolate' ELSE Flavor END)
FROM #Flavors
WHERE isAvailable = 1
GROUP BY Region;
GO
DROP TABLE #Flavors;
EDIT: Also, COUNT() counts all non-null values, that's why it will count zeros and ones. SUM() would add the values, but it's not necessary in this case as you can avoid reading the zeros entirely.
March 23, 2016 at 12:08 am
You must have a mapping table between varieties of flavors and "flavor groups" (or how do you name it).
When you say "all chocolate flavors" you mean "chocolate", "chocolate swirl", etc.
This knowledge must be passed from your brain to the database, so the computer can interpret the flavor names the way you mean it.
Since I can only guess what is the complete mapping rule, I implemented using "LIKE" matching:
SELECT Region,
COUNT( DISTINCT CASE WHEN isAvailable = 1 THEN P.FlavorGroup ELSE NULL END)
FROM (
SELECT 'chocolate', 'chocolate%'
UNION ALL
SELECT 'vanilla', 'vanilla%'
UNION ALL
SELECT 'french vanilla', 'french vanilla%'
UNION ALL
SELECT 'mint chocolate', 'mint chocolate%'
UNION ALL
SELECT 'strawberry', 'strawberry%'
UNION ALL
SELECT 'butterscotch', 'butterscotch%'
) P (FlavorGroup, FlavorPattern)
LEFT JOIN #Flavors T ON T.Flavor LIKE P.FlavorPattern
GROUP BY Region ;
_____________
Code for TallyGenerator
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply