a lot of count()s

• Jan Jaap van der Most

  SSC Journeyman

  Points: 92

  More actions

  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

• mccork

  SSC-Insane

  Points: 22288

  More actions

  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

• cvandemaele

  SSC Veteran

  Points: 208

  More actions

  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.

• Jan Jaap van der Most

  SSC Journeyman

  Points: 92

  More actions

  September 11, 2003 at 5:17 am

  #473672

  Thank you very much both solutions are very helpfull!

  Thanks again,

  Jan Jaap