Why Aren't Nulls Counted in Count(Columnname)

Why aren't nulls counted in COUNT(columnname)

COUNT counts values, since null is not a value it does not get counted.

If you want to count all null values you could do something like this:

SELECT COUNT(ID) as NotNull, SUM(CASE WHEN ID IS NULL then 1 else 0 end) as NullCount

In SQL, what's the difference between count(column) and count(*)?

count(*) counts NULLs and count(column) does not

[edit] added this code so that people can run it

create table #bla(id int,id2 int)
insert #bla values(null,null)
insert #bla values(1,null)
insert #bla values(null,1)
insert #bla values(1,null)
insert #bla values(null,1)
insert #bla values(1,null)
insert #bla values(null,null)

select count(*),count(id),count(id2)
from #bla

results
7 3 2

SQL column to count grouped values

You could use conditional sum:

select   
  c.customer_name
  ,count(*) as counts
  ,SUM(CASE WHEN j.approval_date is not null THEN 1 ELSE 0 END)
from job j
join customer c on j.customer_id = c.customer_id
group by c.customer_name;

Count query giving wrong column name error

Your problems are arising from improper joining of tables. You need information from both Runs and Results, but they aren't combined properly in your query. You have the right idea with a nested subquery, but it's in the wrong spot. You're also referencing the Analysed table in the outer where clause, but it hasn't been included in the from clause.

Try this instead:

select (cast(counts.Count as decimal(10,4)) / cast(failCount as decimal(10,4))) as PercentAnalysed 
from Runs 
inner join
(
  SELECT COUNT(*) AS 'Count', runId 
  FROM Results 
  WHERE Analysed = 'True'
  GROUP BY runId
) counts
on counts.runId = Runs.runId

I've set this up as an inner join to eliminate any runs which don't have analysed results; you can change it to a left join if you want those rows, but will need to add code to handle the null case. I've also added casts to the two numbers, because otherwise the query will perform integer division and truncate any fractional amounts.

How do I check if a column is empty or null in MySQL?

This will select all rows where some_col is NULL or '' (empty string)

SELECT * FROM table WHERE some_col IS NULL OR some_col = '';

Count NULL Values from multiple columns with SQL

SELECT COUNT(*)-COUNT(A) As A, COUNT(*)-COUNT(B) As B, COUNT(*)-COUNT(C) As C
FROM YourTable; 

---

Related Topics