count non null values

  • do we have a select statment which can count only non null values against each column and report the total sum. I have about 19 columns in my table and about 85000 rows.

    I need total sum of non null values in my table.

  • If you want a single result then something like this would do:

    select sum (

    case when is not null then 1 else 0 end

    + case when is not null then 1 else 0 end

    ...

    )

    from

    If you want a count by column then:

    select sum ( case when is not null then 1 else 0 end ) as Col1_Count

    , sum( case when is not null then 1 else 0 end ) as Col2_Count

    ...

    from

    Jez

  • Would you mind to open Book on Line and read about aggregate system functions?

    COUNT, SUM, etc.

    _____________
    Code for TallyGenerator

  • this would give you the sample structure to test each column in a single pass...is that what you wanted, or did you want where an entire row was null?

    select SUM(CASE WHEN COL1 IS NULL THEN 0 ELSE 1 END) AS COL1,

    SUM(CASE WHEN COL2 IS NULL THEN 0 ELSE 1 END) AS COL2

    SUM(CASE WHEN COL3 IS NULL THEN 0 ELSE 1 END) AS COL3

    FROM SOMETABLE

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Jez, same advice to you.

    _____________
    Code for TallyGenerator

  • maybe this will get you going ..

    SELECT CASE t1.[ORDINAL_POSITION] WHEN 1 THEN 'SELECT ' ELSE ' , ' end

    + ' sum( CASE WHEN ' + t1.[COLUMN_NAME] + ' is null then 0 else 1 end ) as Count_' + t1.[COLUMN_NAME]

    + CASE t1.[ORDINAL_POSITION] WHEN t2.[MAX_ORDINAL_POSITION] THEN ' from [' + t1.[TABLE_SCHEMA] + '].[' + t1.[TABLE_NAME] + '] ;' ELSE ' ' end

    FROM [INFORMATION_SCHEMA].[COLUMNS] t1

    INNER JOIN

    ( SELECT [TABLE_NAME], [TABLE_SCHEMA], MAX([ORDINAL_POSITION]) AS MAX_ORDINAL_POSITION

    FROM [INFORMATION_SCHEMA].[COLUMNS]

    GROUP BY [TABLE_NAME], [TABLE_SCHEMA]

    )t2

    ON t1.[TABLE_NAME] = t2.[TABLE_NAME]

    AND t1.[TABLE_SCHEMA] = t2.[TABLE_SCHEMA]

    --WHERE t1.[TABLE_NAME] = 'mytable'

    ORDER BY t1.[TABLE_NAME], t1.[TABLE_SCHEMA], t1.[ORDINAL_POSITION]

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • SELECT COUNT(Col1),

    COUNT(Col2),

    COUNT(Col3),

    COUNT(*)

    FROM Table1


    N 56°04'39.16"
    E 12°55'05.25"

  • Thanks all,

    Now another issue,

    I have a column named 'Temp'. If used in query, SQL takes it as a system name, and highlights it blue.

    Any workaround??

    Its not possible for me to change the column name.

  • just put brackets around the columnname ( look at my previous reply 😉 )

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply