Counting null values as unique value
count, null, oracle, sql
Solution
select count(distinct col1) + count(distinct case when col1 is null then 1 end)
from YourTable
Problem
I need to count different values on a column, such as: ``` Hours 1 1 2 null null null ``` The result must be: 3. My query is: ``` select count(distinct hour) from hours; ``` but it returns: 2. I tested also: ``` select count(*) from hours group by hour ``` but it returns three rows: ``` (1) 3 (2) 2 (3) 1 ``` How can I count null values as 1 value and use distinct to avoid count repeated values? I'm learning advanced SQL, they want me these requirements for all the solutions: Try to minimize the number of subqueries you need to solve the query. Furthermore, you are not allowed to use the following constructions: - SELECT in the FROM or SELECT. You are allowed to have subqueries (SELECT in the WHERE or HAVING) - Combinations of aggregation functions such as COUNT (COUNT. ..)), SUM (COUNT. ..)) and the like. - UNION if you can avoid it. - Non-standard functions (such as NVL) - CASE