Post reply

the number of null row(s)

  • January 23, 2008 at 2:25 am

    #182281

    Hi All,

    DROP TABLE #temp

    CREATE TABLE #temp(SID INT,SName VARCHAR(20))

    insert #temp(SID,SName)

    SELECT NULL,'Zero'

    UNION ALL

    SELECT 1,'First'

    UNION ALL

    SELECT 2,'Second'

    UNION ALL

    SELECT 3,'Third'

    UNION ALL

    SELECT NULL,'Fourth'

    SELECT * FROM #temp

    From this how can i get, the number of null row(s) for the particular column.

    Thanks in Advance.

  • January 23, 2008 at 2:48 am

    #770800

    Is this what you have in mind?

    SELECT SUM(CASE WHEN SID IS NULL THEN 1

    ELSE 0

    END)

    FROM #temp

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • January 23, 2008 at 2:54 am

    #770804

    Hi Andras,

    Yes. Thanks you very much for your quick reply. 😛

  • January 23, 2008 at 3:00 am

    #770807

    sqluser (1/23/2008)

    Hi Andras,

    Yes. Thanks you very much for your quick reply. 😛

    I'm glad I could help. A lot of thanks to you for forming the question in an excellent way, i.e. providing a script that creates the necessary tables and populates them with test data.

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • January 24, 2008 at 12:41 pm

    #771474

    This one's a little more ugly:

    SELECT COUNT(*) - COUNT(SID) FROM #temp

    The COUNT() function will only count non-null columns if a column is specified.

    Todd Fifield

Viewing 5 posts - 1 through 4 (of 4 total)

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