COUNT DISTINCT on multiple columns with Conditions

Question

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)

Your query does not make sense. What do you want to achieve? — juergen d
– juergen d, Commented Aug 23, 2018 at 5:21
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
– Crusaderpyro, Commented Aug 23, 2018 at 5:23
Use it for what? Count them how?? Or do you want to sum them? — juergen d
– juergen d, Commented Aug 23, 2018 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
– AxelH, Commented Aug 23, 2018 at 5:26

nosh · Accepted Answer · 2018-08-23 05:23:58Z

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

edited Aug 23, 2018 at 5:23

answered Aug 23, 2018 at 5:18

nosh

6004 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

nosh Over a year ago

Yes. Edited the answer.

nosh Over a year ago

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

Crusaderpyro Over a year ago

That is ok.. the query in the question was jsut a sample data and not the actual columns

Collectives™ on Stack Overflow

COUNT DISTINCT on multiple columns with Conditions

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related