Counting null values as unique value

Question

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 1

select  count(distinct col1) + count(distinct case when col1 is null then 1 end)
from    YourTable

Answer 2

if hour is a number, then if it can only be an integer:

select count(distinct coalesce(hour, 0.1)) cnt from test;

otherwise if it can be any floating point, change NULL to a char string.

eg

select count(distinct coalesce(to_char(hour), 'a')) cnt from test;

Answer 3

select 
   count(0) 
from
  (
      select distinct hour from hours
  )

SqlFiddle

Answer 4

SELECT
      ( SELECT COUNT(DISTINCT hour)
        FROM hours
      )
    + CASE WHEN EXISTS
           ( SELECT *
             FROM hours
             WHERE hour IS NULL
           )
        THEN 1 
        ELSE 0
      END
   AS result
FROM dual ;

Answer 5

maybe

    select count(distinct hour||' ') from hours;

will do?

Answer 6

select count(distinct nvl(hour,0)) from hours;

Answer 7

I'd say your requirements are pretty bizarre, given that you're almost certain to get a more efficient query simply using NVL(), COALESCE() or CASE. However, I managed to get the right result (and cope with the presence or absence of NULL values) using only subqueries. I've not managed to do this without using a subquery in the FROM clause yet.

SQL Fiddle

Query 1:

SELECT nnh.not_null_hours + nh.null_hours
FROM (
  SELECT COUNT(DISTINCT t.hour) not_null_hours
  FROM example_table t
) nnh
CROSS JOIN (
  SELECT 1 null_hours
  FROM dual
  WHERE EXISTS (
    SELECT 1
    FROM example_table t
    WHERE t.hour IS NULL
  )
  UNION ALL
  SELECT 0 null_hours
  FROM dual
  WHERE NOT EXISTS (
    SELECT 1
    FROM example_table t
    WHERE t.hour IS NULL
  )
) nh

Results:

| NNH.NOT_NULL_HOURS+NH.NULL_HOURS |
------------------------------------
|                                3 |

This is going to a lot of effort to cope with the requirements. A much simpler option is to use NVL, and then one of two simple choices... either:

1. Use TO_CHAR to convert the non-NULL values to the datatype VARCHAR2 and NVL to convert NULL values to the VARCHAR2 'NULL' or
2. Just use NVL with a magic number that you know will never be present in the result set (i.e. because of constraints on the table).

Query 1:

SELECT 
  COUNT(DISTINCT NVL(TO_CHAR(hour), 'NULL')) using_to_char_null
, COUNT(DISTINCT NVL(hour, -1)) using_magic_number
FROM example_table

Results:

| USING_TO_CHAR_NULL | USING_MAGIC_NUMBER |
-------------------------------------------
|                  3 |                  3 |

Answer 8

Answer by Andres is the one that meets the requirements perfectly and without using any function at all apart from COUNT:

select count(distinct hour||' ') from hours;

i was looking for same thing for another purpose ( I could use anything at all ) but it did not seem correct or efficient to me until I saw this one, thank you Andres, such a simple solution yet a powerful one.

Answer 9

The closest I could get fitting the criteria specified is this: (SQL Fiddle)

Query 1:

SELECT COUNT(*)
FROM example_table t1
WHERE t1.ROWID IN (
  SELECT MAX(t2.ROWID)
  FROM example_table t2
  GROUP BY t2.hour
)

Results:

| COUNT(*) |
------------
|        3 |

Not sure if the ROWID pseudocolumn is allowed, given the other restrictions, but it works and gracefully handles NULL values. I don't think ROWID exists outside of Oracle, so likely this is going against the spirit of the question, but it fits the criteria listed at least.

Answer 10

Probably the easiest way is to use DUMP:

SELECT COUNT(DISTINCT DUMP(hour)) AS distinct_count
FROM hours;

Output: 3

DBFiddle Demo

Answer 11

Ah.. homework. Isn't it as simple as this?

SELECT COUNT(hour) 
  FROM hours

NULLS don't get counted.

Got it! My bad for not reading the requirements properly.

SELECT COUNT(DISTINCT COALESCE(hour,-1)) 
  FROM hours