Post reply

a lot of count()s

  • September 11, 2003 at 12:22 am

    #176708

    I am counting the non-null values of a column. Normally that could be done by a GROUP BY clause. But than I have to do one query per column and I have > 25 columns.

    Does anyone know a possibility to COUNT in a way like COUNT(ISNULL(column, FALSE)), so that null values are not counted and that I can combine the counting of the columns?

    Thanks

    Jan Jaap, Netherlands

  • September 11, 2003 at 12:42 am

    #473615

    an example:

     

    SELECT 

        Null_discounttype = SUM(CASE WHEN [discounttype] IS NULL THEN 1 ELSE 0 END), 

        Null_stor_id = SUM(CASE WHEN [stor_id] IS NULL THEN 1 ELSE 0 END), 

        Null_lowqty = SUM(CASE WHEN [lowqty] IS NULL THEN 1 ELSE 0 END), 

        Null_highqty = SUM(CASE WHEN [highqty] IS NULL THEN 1 ELSE 0 END), 

        Null_discount = SUM(CASE WHEN [discount] IS NULL THEN 1 ELSE 0 END)

    FROM [pubs].[dbo].[discounts]

    Cheers,

    - Mark


    Cheers,
    - Mark

  • September 11, 2003 at 2:15 am

    #473632

    Hi there.

    The COUNT() function eliminates null values if you specify the column name. Check out BOL !!

    Example :

    SELECT COUNT(*), COUNT(TITLE), COUNT(TITLEOFCOURTESY) from northwind.employees

    will return

    first column : number of rows in table

    second column : number of rows having non-null values in the TITLE column

    third column : number of rows having non-null values in the TITLEOFCOURTESY column

    Regards.

    CVM.

  • September 11, 2003 at 5:17 am

    #473672

    Thank you very much both solutions are very helpfull!

    Thanks again,

    Jan Jaap

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

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