conditionally count method

  • 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!

  • 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
  • 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