conditionally count method

Question

Post reply

conditionally count method

Magy

SSCarpal Tunnel

Points: 4217
More actions
March 22, 2016 at 10:45 am

#305841

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!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183686 More actions · Answer 1

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sergiy SSC Guru Points: 110192 More actions · Answer 2

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