SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

suryayadav
Starting Member

India
4 Posts

Posted - 11/13/2013 :  11:46:06  Show Profile  Reply with Quote
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
--------------
surya

surya

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 11/13/2013 :  17:32:28  Show Profile  Reply with Quote
quote:
Originally posted by suryayadav

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
--------------
surya

surya

Didn't quite get what you meant by "the execution flow has to be like whenever i come across a null the counter has to be incremented by 1." Can you explain?

If you what to find the number of nulls, you can write a query such as shown below:
SELECT
	SUM(CASE WHEN productid IS NULL THEN 1 ELSE 0) END AS NullProductId,
	SUM(CASE WHEN productname IS NULL THEN 1 ELSE 0) END AS Nullproductname,
	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;
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17434 Posts

Posted - 11/13/2013 :  22:58:23  Show Profile  Reply with Quote
or just simply

SELECT 
       COUNT(*) - COUNT(productid) as NullProductId
FROM   
       Products;



KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/14/2013 :  02:23:04  Show Profile  Reply with Quote
do you want to count number of NULL values per column or do you want total number of NULL values per row?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

suryayadav
Starting Member

India
4 Posts

Posted - 11/14/2013 :  06:48:44  Show Profile  Reply with Quote
i want to count the number of null columns in a row

surya
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/14/2013 :  07:28:33  Show Profile  Reply with Quote
DO you've a PK column which identifies a row?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17434 Posts

Posted - 11/14/2013 :  08:48:22  Show Profile  Reply with Quote
select	productid, null_cols = case when productname is null then 1 else 0 end
			     + case when manufactureDate is null then 1 else 0 end
			     + case when DeliveryDateis null then 1 else 0 end
from	Products



KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000