Clash Royale CLAN TAG#URR8PPP

Selecting a value contained in a conditional group by in SQL

Depending on the @dataCase value, the result would be either grouped by country or by country and city.

SELECT
User.CountryId AS Country
FROM User
GROUP BY User.CountryId,
CASE
WHEN @dataCase = 1 THEN User.City
END


But in the case when the result is grouped by City I can't get the value. It shows the following error.

"Column 'User.City' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

SELECT
User.CountryId AS Country,
CASE
WHEN @dataCase != 1 THEN '-'
WHEN @dataCase = 1 THEN User.City
END AS City
FROM User
GROUP BY User.CountryId,
CASE
WHEN @dataCase = 1 THEN User.City
END


Is it at all possible to select a value that is inside a conditional group by in SQL?

2 Answers
2

The SELECT expression needs to match the GROUP BY expression. So this should work:

SELECT

GROUP BY

SELECT u.CountryId AS Country,
(CASE WHEN @dataCase = 1 THEN u.City
END) AS City
FROM User u
GROUP BY User.CountryId,
(CASE WHEN @dataCase = 1 THEN u.City
END);


If you want '-' instead:

'-'

SELECT u.CountryId AS Country,
(CASE WHEN @dataCase = 1 THEN u.City ELSE '-'
END) AS City
FROM User u
GROUP BY User.CountryId,
(CASE WHEN @dataCase = 1 THEN u.City ELSE '-'
END);


group by

select distinct

Since you don't have any aggregation function involved, you can use DISTINCT instead :

DISTINCT

SELECT DISTINCT u.CountryId AS Country,
(CASE WHEN @dataCase = 1 THEN u.City
ELSE '-'
END) AS City
FROM [User] u;


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.