David David - 1 year ago 140
SQL Question

Counting null values as unique value

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


Answer Source
select  count(distinct col1) + count(distinct case when col1 is null then 1 end)
from    YourTable
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download