Counting null and non-null values in a single query

Question

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

User · Answer

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

User · Answer

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

User · Answer

Here are two solutions   Select count columnname  as countofNotNulls  count isnull columnname 1  -count columnname  AS Countofnulls from table name   OR  Select count columnname  as countofNotNulls  count   -count columnname  AS Countofnulls from table name

User · Answer

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

User · Answer

I created the table in postgres 10 and both of the following worked   select count    from us  and  select count a is null  from us

User · Answer

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  Better in that the column titles come out correct   SELECT COUNT A  NOT NULL  COUNT    - COUNT A  NULLS FROM US   In some testing on my system  it costs a full table scan

User · Answer

Try  SELECT     SUM ISNULL a   AS all null     SUM  ISNULL a   AS all not null FROM us    Simple

User · Answer

Try this    SELECT CASE           WHEN a IS NULL THEN  Null            ELSE  Not Null          END a          Count 1   FROM   us  GROUP  BY CASE              WHEN a IS NULL THEN  Null               ELSE  Not Null             END

User · Answer

Just to provide yet another alternative  Postgres 9 4  allows applying a FILTER to aggregates   SELECT   COUNT    FILTER  WHERE a IS NULL  count nulls    COUNT    FILTER  WHERE a IS NOT NULL  count not nulls FROM us    SQLFiddle  http   sqlfiddle com   17 80a24 5

User · Answer

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

User · Answer

As i understood your query  You just run this script and get Total Null Total NotNull rows   select count    - count a  as  Null   count a  as  Not Null  from us

User · Answer

In my case I wanted the  null distribution  amongst multiple columns   SELECT         CASE WHEN a IS NULL THEN  NULL  ELSE  NOT-NULL  END  AS a null          CASE WHEN b IS NULL THEN  NULL  ELSE  NOT-NULL  END  AS b null          CASE WHEN c IS NULL THEN  NULL  ELSE  NOT-NULL  END  AS c null                    count    FROM us GROUP BY 1  2  3     ORDER BY 1  2  3       As per the       it is easily extendable to more columns  as many as needed

User · Answer

use ISNULL embedded function

User · Answer

SELECT     ALL VALUES      COUNT ALL VALUES  FROM          SELECT          NVL2 A  NOT NULL   NULL   AS ALL VALUES           NVL A 0          FROM US   GROUP BY ALL VALUES

User · Answer

This is little tricky  Assume the table has just one column  then the Count 1  and Count    will give different values   set nocount on     declare  table1 table  empid int      insert  table1 values  1   2   3   4   5   6   7   8   9   10   NULL   11   12   NULL   13   14        select   from  table1     select COUNT 1  as  COUNT 1   from  table1     select COUNT empid   Count empid   from  table1   Query Results  As you can see in the image  The first result shows the table has 16 rows  out of which two rows are NULL  So when we use Count    the query engine counts the number of rows  So we got count result as 16  But in case of Count empid  it counted the non-NULL-values in the column empid  So we got the result as 14    so whenever we are using COUNT Column  make sure we take care of NULL values as shown below   select COUNT isnull empid 1   from  table1   will count both NULL and Non-NULL values   Note  Same thing applies even when the table is made up of more than one column  Count 1  will give total number of rows irrespective of NULL Non-NULL values  Only when the column values are counted using Count Column  we need to take care of NULL values

User · Answer

for counting not null values  select count    from us where a is not null    for counting null values   select count    from us where a is null

User · Answer

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

User · Answer

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       SELECT COUNT      null tally  AS narrative    FROM us   WHERE a IS NULL  UNION SELECT COUNT      not null tally  AS narrative    FROM us   WHERE a IS NOT NULL

User · Answer

Building off of Alberto  I added the rollup    SELECT  Narrative    CASE   WHEN  Narrative  IS NULL THEN  count total  ELSE     Narrative  END   Count  SUM  Count   FROM  SELECT COUNT     Count    count nulls  AS  Narrative    FROM  CrmDW   CRM   User    WHERE  EmployeeID  IS NULL  UNION SELECT COUNT      count not nulls   AS narrative  FROM  CrmDW   CRM   User   WHERE  EmployeeID  IS NOT NULL  S  GROUP BY  Narrative  WITH CUBE

User · Answer

I had a similar issue  to count all distinct values  counting null values as 1  too  A simple count doesn t work in this case  as it does not take null values into account   Here s a snippet that works on SQL and does not involve selection of new values  Basically  once performed the distinct  also return the row number in a new column  n  using the row number   function  then perform a count on that column   SELECT COUNT n  FROM       SELECT    row number   OVER  ORDER BY  MyColumn  ASC  n     FROM           SELECT DISTINCT  MyColumn                      FROM  MyTable            items     distinctItems

User · Answer

This works in T-SQL  If you re just counting the number of something and you want to include the nulls  use COALESCE instead of case   IF OBJECT ID  tempdb   us   IS NOT NULL     DROP TABLE  us  CREATE TABLE  us           a INT NULL         INSERT INTO  us VALUES  1   2   3   4   NULL   NULL   NULL   8   9   SELECT   FROM  us  SELECT CASE WHEN a IS NULL THEN  NULL  ELSE  NON-NULL  END AS  NULL            COUNT CASE WHEN a IS NULL THEN  NULL  ELSE  NON-NULL  END  AS  Count      FROM  us     GROUP BY CASE WHEN a IS NULL THEN  NULL  ELSE  NON-NULL  END  SELECT COALESCE CAST a AS NVARCHAR   NULL   AS a          COUNT COALESCE CAST a AS NVARCHAR   NULL    AS  Count      FROM  us     GROUP BY COALESCE CAST a AS NVARCHAR   NULL

User · Answer

All the answers are either wrong or extremely out of date   The simple and correct way of doing this query is using COUNT IF function   SELECT   COUNT IF a IS NULL  AS nulls    COUNT IF a IS NOT NULL  AS not nulls FROM   us

User · Answer

usually i use this trick  select sum case when a is null then 0 else 1 end  as count notnull         sum case when a is null then 1 else 0 end  as count null from tab group by a

User · Answer

Just in case you wanted it in a single record   select     select count    from tbl where colName is null  Nulls     select count    from tbl where colName is not null  NonNulls     -

User · Answer

select count isnull NullableColumn -1

User · Answer

This works for Oracle and SQL Server  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

[sql] Counting null and non-null values in a single query

Examples related to sql