Query

• journey to ssc

  Grasshopper

  Points: 14

  More actions

  November 13, 2013 at 9:32 am

  #300141

  Hi all,

  I am facing a problem in writing a query.

  Here is my requirement

  i have a <products> table with columns <productid> <productname> <manufactureDate> <DeliveryDate>

  and some columns are filled with null values

  i am trying to find the number of null columns with a counter.

  the execution flow has to be like whenever i come across a null the counter has to be incremented by 1.

  kindly help me in writing this query.

  Regards

  --------------

  Trainee SQL

• Lowell

  SSC Guru

  Points: 323518

  More actions

  November 13, 2013 at 9:42 am

  #1666134

  what you want to do here is use some customa ggregation:

  a SUM(CASE statement can help you generate the counts in a single statement

  something like this:

  SELECT

  COUNT(productid) AS Totalproduct,

  SUM(CASE WHEN productname IS NULL THEN 1 ELSE 0 END) AS Nullproductnames,

  SUM(CASE WHEN manufactureDate IS NULL THEN 1 ELSE 0 END) AS NullmanufactureDate,

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

  FROM products

  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!

• Dawn W

  Ten Centuries

  Points: 1010

  More actions

  November 13, 2013 at 9:47 am

  #1666136

  Search for DO WHILE, Cursors, and variables. You'll learn more if you figure it out yourself.

  If you really want to learn more, look on Microsoft's site for webcasts and read the Stairways on this site.

• Phil Parkin

  SSC Guru

  Points: 246965

  More actions

  November 13, 2013 at 9:56 am

  #1666142

  It's not totally clear how you want to see the results. The following queries give you a count of NULLs in each column, for all rows in the table:

  declare @Rows int

  select @Rows = count(1)

  from Product

  select Col1Nulls = (@Rows - count(Col1))

  ,Col2Nulls = (@Rows - count(Col2))

  from Product

  If that's not what you need, please read the link in my signature and provide DDL, sample data and desired results.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin