I have a table
create table us
(
a number
);
Now I have data like:
a
1
2
3
4
null
null
null
8
9
Now I need a single query to count null and not null values in column a
views:
457answers:
8
A:
Number of elements where a is null:
select count(a) from us where a is null;
Number of elements where a is not null:
select count(a) from us where a is not null;
romaintaz
2009-08-13 13:06:03
A:
for non nulls
select count(a)
from us
for nulls
select count(*)
from us
minus
select count(a)
from us
Hence
SELECT COUNT(A) NOT_NULLS
FROM US
UNION
SELECT COUNT(*) - COUNT(A) NULLS
FROM US
ought to do the job
EvilTeach
2009-08-13 13:06:25
Good gravy, man, look at the execution plans for these queries. You're kicking off table scans left and right, especially where there's such a bloody simple statement (`select count(*) from t where a is null`) that does this.
Eric
2009-08-13 13:15:35
I don't have a database handy to look, but either the column is indexed or not. If it is, it happens via a range scan, otherwise, you are pretty much left with a full table scan. In oracle, NULLs are not stored in the index, so I suspect you example isn't much better. Your milage may very.
EvilTeach
2009-08-13 13:19:52
@EvilTeach: Indexes are only helpful when you're not pulling back > ~10% of the rows. After that, full scans are initiated. In this case, you'll get the scan at least once, if not twice.
Eric
2009-08-13 13:22:16
thanx every body
2009-08-13 13:34:41
A:
if its mysql, you can try something like this.
select
(select count(*) from TABLENAME WHERE a = 'null') as total_null,
(select count(*) from TABLENAME WHERE a != 'null') as total_not_null
FROM TABLENAME
Wbdvlpr
2009-08-13 13:06:27
+5
A:
Here is a quick and dirty version that works on Oracle :
select sum(case a when null then 1 else 0) "Null values",
sum(case a when null then 0 else 1) "Non-null values"
from us
Altherac
2009-08-13 13:06:58
Similar syntax would work in SQL Server as well. Also, doing it this way will only scan the table once; the UNION solutions will do two table scans. Irrelevant for small tables, very important for huge ones.
Philip Kelley
2009-08-13 13:37:37
Only change for SQL Server is `"Null values"` would have to become `'Null values'`. Single quotes, not doubles.
Eric
2009-08-13 13:42:54
SQLServer uses an *index scan* for this query vs two *index seeks* using a union. On a table with 40.000 rows, there is no speed difference.
Lieven
2009-08-13 13:45:35
On a table with 11.332.581 rows, there are two *table scans*, no noticable speed difference (actualy, the union is *slightly* faster).
Lieven
2009-08-13 13:50:04
+8
A:
If I understood correctly you want to count all NULL and all NOT NULL in a column...
If that is correct:
SELECT count(*) FROM us WHERE a IS NULL
UNION ALL
SELECT count(*) FROM us WHERE a IS NOT NULL
Edited to have the full query, after reading the comments :]
Alberto Zaccagni
2009-08-13 13:06:58
+1: By far the simplest, quickest way. I was shocked when every answer wasn't this.
Eric
2009-08-13 13:16:24
Yes but no. I think he wants to have the number of NULL and not NULL in only one query... You are saying how to do that in two queries...
romaintaz
2009-08-13 13:25:15
@romaintaz: Quite right. I read the title as the question. In five edits, nobody thought to fix it. Yeesh.
Eric
2009-08-13 13:29:11
@romaintaz: Yes you are right, I've taken this as a "run once query to spot how many nulls we have", I don't even know why ^^', going to correct, thanks.
Alberto Zaccagni
2009-08-13 13:29:43
@Eric: Oh thanks, I'm not that mad then... :P the question changed a lot since I first read it.
Alberto Zaccagni
2009-08-13 13:33:36
+3
A:
Hi, This works for Oracle, you might be able to get it to work on another RDBMS:
select sum(case when a is null then 1 else 0 end)) count_nulls
, count(a) count_not_nulls
from us;
Or:
select count(*) - count(a), count(a) from us;
Cheers,
MD
Emdee
2009-08-13 13:09:14
A:
If you're using MS Sql Server...
SELECT COUNT(0) AS 'Null_ColumnA_Records',
(
SELECT COUNT(0)
FROM your_table
WHERE ColumnA IS NOT NULL
) AS 'NOT_Null_ColumnA_Records'
FROM your_table
WHERE ColumnA IS NULL;
I don't recomend you doing this... but here you have it (in the same table as result)
Andrei
2009-08-13 13:10:52
A:
SELECT SUM(NULLs) AS 'NULLS', SUM(NOTNULLs) AS 'NOTNULLs' FROM
(select count(*) AS 'NULLs', 0 as 'NOTNULLs' FROM us WHERE a is null
UNION select 0 as 'NULLs', count(*) AS 'NOTNULLs' FROM us WHERE a is not null) AS x
It's fugly, but it will return a single record with 2 cols indicating the count of nulls vs non nulls.
C-Pound Guru
2009-08-13 13:38:18