Query - SQL Server Forums

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;

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]

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

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

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

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]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources