Count function in case if condition

Question

Can someone explain the difference between the SQL statements below? I see that there is a difference, but I can’t cover up the exact conditions that can make them produce different results. By the way, I think that the distinct clause does not affect the user.id field, since all identifiers are already unique. The purpose of the request is to count the number of unique (non-empty) names. If the surname is empty, then consider it unique.

I believe that a common case for this problem would be to use an aggregate function in a case-when statement.

Count in Case-When:

 SELECT (case when (substr(u.name,40,40) <> ' ') then count(distinct(substr(u.name,40,40))) else count(u.id) end) as "LAST_NAME", FROM users u GROUP BY substr(u.name,40,40) 

Case - when inside Count:

 SELECT count (distinct case when (substr(u.name,40,40) <> ' ') then substr(u.name,40,40) else to_char(u.id) end) as "LAST_NAME", FROM users u GROUP BY substr(u.name,40,40) 

Answer 1

If user.id is a PRIMARY KEY , these queries are semantically identical, although they can create different execution plans.

They will return 1 for all non-empty names, since you count the different values ​​of group expiration within your group, which by definition will be exactly the same.

For empty last names, the first query will require COUNT(u.id) , and the second will return COUNT(DISTINCT TO_CHAR(u.id)) , which, provided that u.id is unique, is the same.

I think you need to remove GROUP BY from the second query:

 SELECT count (distinct case when (substr(u.name,40,40) <> ' ') then substr(u.name,40,40) else to_char(u.id) end) as "LAST_NAME", FROM users u