Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Counting null values as unique value

Tags:

People also ask

How do I count distinct NULL values in SQL?

SELECT COUNT (DISTINCT item_num) FROM items; If the COUNT DISTINCT function encounters NULL values, it ignores them unless every value in the specified column is NULL. If every column value is NULL, the COUNT DISTINCT function returns zero (0).

Is NULL counted in count distinct?

NULL values are not included in COUNT DISTINCT counts.

Is NULL a unique value?

A null value in a relational database is used when the value in a column is unknown or missing. A null is neither an empty string (for character or datetime data types) nor a zero value (for numeric data types).


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