Using sum function with when case

Question

SELECT  '2017-05-30' AS `short_date`,
        'Stats' AS `group`,
        'Active ' AS `subgroup`,
         '' AS `row`,
         CASE
WHEN COUNT(DISTINCT(guild_name)) >= 1 AND  COUNT(DISTINCT(guild_name)) <= 5   THEN '1 - 5 ' 
WHEN COUNT(DISTINCT(guild_name)) >= 6 AND COUNT(DISTINCT(guild_name)) <= 10  THEN '6 - 10 '
WHEN COUNT(DISTINCT(guild_name)) >= 11 AND COUNT(DISTINCT(guild_name)) <= 15 THEN '11 - 15 '
WHEN COUNT(DISTINCT(guild_name)) >= 16 AND COUNT(DISTINCT(guild_name)) <= 20 THEN '16 - 20 '
WHEN COUNT(DISTINCT(guild_name)) >= 21 AND COUNT(DISTINCT(guild_name)) <= 25 THEN '21 - 25 '
WHEN COUNT(DISTINCT(guild_name)) >= 30 THEN '> 30' END AS `value`
FROM  table
WHERE guild_name !=0
GROUP BY
                `short_date`,
                `group`,
                `subgroup`

How not to count a value in each case, how to count it once and use in each case?

Your question looks like https://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql — Martin Verjans, Jun 06 '17 at 15:52
You are putting in strings for your three GROUP BY fields? All this query will return is a single line with the bucket of distinct guild_names. Are you planning to later expand this to run against multiple short_dates, groups and subgroups? — ScottieB, Jun 06 '17 at 15:53

score 3 · Accepted Answer · answered Jun 06 '17 at 17:10

How about you move the count to a subquery and move the case outside like this:

select `short_date`, `group`, `subgroup`, `row`,   
CASE
    WHEN nb_guild >= 1 AND nb_guild <= 5   THEN '1 - 5 ' 
    WHEN nb_guild >= 6 AND nb_guild <= 10  THEN '6 - 10 '
    WHEN nb_guild >= 11 AND nb_guild <= 15 THEN '11 - 15 '
    WHEN nb_guild >= 16 AND nb_guild <= 20 THEN '16 - 20 '
    WHEN nb_guild >= 21 AND nb_guild <= 25 THEN '21 - 25 '
    WHEN nb_guild >= 30 THEN '> 30' 
END AS `value`
from 
(
    SELECT  '2017-05-30' AS `short_date`,
            'Stats' AS `group`,
            'Active ' AS `subgroup`,
            '' AS `row`,
            COUNT(DISTINCT(guild_name)) as nb_guild
    FROM  table
    WHERE guild_name !=0
    GROUP BY
                    `short_date`,
                    `group`,
                    `subgroup`
) as subquery

This way your count only gets executed once in the subquery and your outer query does its treatment on the resulting resultset.

Using sum function with when case

1 Answers1