Not able to get the result with CASE and COUNT syntax

1

Hi,

I am trying to use CASE syntax with in COUNT but not able get the result. Need some help please.

Table : Is with list of projects from different countries.
What I am trying to do : Trying to get the count of projects grouped by country and then using case statement to assign a name for each country based on their total number of projects.

SELECT Country, Count (Project_Name) AS Project_Count,
CASE
WHEN Project_Name > 30 THEN 'Group A'
WHEN Project_Name between 50 and 100 THEN 'Group B'
ELSE 'Group C'
END AS Group_Result
FROM [Active Projects]
GROUP BY Country;

Error : Column 'Active Projects.Project_Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

2

When using a case statement you should add the case also to the group by statment, like this:

SELECT Country, Count (Project_Name) AS Project_Count,
CASE
WHEN Project_Name > 30 THEN 'Group A'
WHEN Project_Name between 50 and 100 THEN 'Group B'
ELSE 'Group C'
END AS Group_Result
FROM [Active Projects]
GROUP BY Country,
CASE
WHEN Project_Name > 30 THEN 'Group A'
WHEN Project_Name between 50 and 100 THEN 'Group B'
ELSE 'Group C'
END;
1 Like
3

Or maybe this:


SELECT Country, Count (Project_Name) AS Project_Count,
CASE
WHEN COUNT(Project_Name) > 30 THEN 'Group A'
WHEN COUNT(Project_Name) between 50 and 100 THEN 'Group B'
ELSE 'Group C'
END AS Group_Result
FROM [Active Projects]
GROUP BY Country;
1 Like
4

Thank you very much but, I am encountering the below error. I understand that error is telling me that I am trying to use a character string as if it was an integer but, we are the considering count values column is it !!

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'Formodual Mini GEMA 5.2 ESP 1.0 RTE' to data type int.

5

Thank you very much, it works perfectly... But the out put result is not as defined in when statements. Any count which is more then 100 is showing as 'Group A' and the rest under 'Group C'

6

For some reason between statement is not working. So I have used the below lines.

WHEN Count (Project_Name) > 100 THEN 'Group A'
WHEN Count (Project_Name) > 50 THEN 'Group B'
ELSE 'Group C'
7

To explain why you were having issues - a CASE expression evaluates the conditions in order and stops evaluating as soon as a condition is true.

CASE
WHEN COUNT(Project_Name) > 30 THEN 'Group A'
WHEN COUNT(Project_Name) between 50 and 100 THEN 'Group B'
ELSE 'Group C'

In this statement - if the count is > 30 it will return 'Group A' - even if the count is 50 or greater it will always return that value because that is the first true expression.

Reversing the order - as your final solution shows works because each expression will be evaluated until a true is found - so if the total count is 29 the ELSE is returned - if value is 51 then > 50 is true - and so on.

1 Like
8

I have a feeling you've got an error in your conditions, but this would match what you've posted so far:

CASE
WHEN COUNT(Project_Name) < 30 OR COUNT(Project_Name) > 100 THEN 'Group C'
WHEN COUNT(Project_Name) between 50 and 100 THEN 'Group B'
ELSE 'Group A'
END AS Group_Result

1 Like
9

Thank you very much