COUNT DISTINCT on multiple columns with Conditions
COUNT DISTINCT on multiple columns with Conditions
I need to do a count on distinct of multiple columns with case condition
I already know the answer given here if I have just 1 column. But if I have multiple columns as follows:
SELECT COUNT(DISTINCT (CASE WHEN CustomerId > 10 THEN CITY,COUNTRY END))
FROM Customers;
The error I get is
Error 1: could not prepare statement (1 near ",": syntax error)
Yeah it was not supposed to be a sensible query as I cannot copy paste the actual sensitive column and table data. I just wanted to use multiple columns in the Then clause of the distinct
– Crusaderpyro
Aug 23 at 5:23
Use it for what? Count them how?? Or do you want to sum them?
– juergen d
Aug 23 at 5:23
Your case statement try to return two column
THEN CITY,COUNTRY END which, AFAIK isn't accepted. You need to use a work around for that. Either duplicate the case, on for each cit or simply use a where condition and a group by.– AxelH
Aug 23 at 5:26
THEN CITY,COUNTRY END
where
1 Answer
1
I'm not sure what your columns are but you could always just group.
SELECT
COUNT(1),
concat(CITY, ",", COUNTRY) as location
FROM Customers where CustomerId > 10
GROUP BY location
Basically this will count the number of 1's for each unique combination of CITY and COUNTRY.
Also, if you wanted to count distinct locations, just concat in the CASE THEN clause
SELECT COUNT(
DISTINCT (CASE WHEN CustomerId > 10
THEN CONCAT(CITY,",",COUNTRY) END)
)
FROM Customers;
Your "Error 1: could not prepare statement (1 near ",": syntax error)" arises because the THEN CLAUSE expects 1 arg without a comma
does this work with the case statement ?
– Crusaderpyro
Aug 23 at 5:21
Yes. Edited the answer.
– Nosh
Aug 23 at 5:24
Also note that your count will be off by 1. This is because your case statement will result in a null when the CustomerId <= 10
– Nosh
Aug 23 at 5:26
That is ok.. the query in the question was jsut a sample data and not the actual columns
– Crusaderpyro
Aug 23 at 5:27
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.
Your query does not make sense. What do you want to achieve?
– juergen d
Aug 23 at 5:21