Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Query

Author  Topic 

suryayadav
Starting Member

4 Posts

Posted - 2013-11-13 : 11:46:06
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-13 : 17:32:28
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)

17689 Posts

Posted - 2013-11-13 : 22:58:23
or just simply

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



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-14 : 02:23:04
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

4 Posts

Posted - 2013-11-14 : 06:48:44
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

52326 Posts

Posted - 2013-11-14 : 07:28:33
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)

17689 Posts

Posted - 2013-11-14 : 08:48:22
[code]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
[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
   

- Advertisement -