Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Expand / Collapse
Author
Message
Posted Wednesday, November 13, 2013 9:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 13, 2013 9:46 AM
Points: 4, Visits: 7
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
Post #1513950
Posted Wednesday, November 13, 2013 9:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 12,905, Visits: 32,166
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1513958
Posted Wednesday, November 13, 2013 9:47 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 3:59 PM
Points: 321, Visits: 436
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.
Post #1513960
Posted Wednesday, November 13, 2013 9:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 5,180, Visits: 12,033
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1513969
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse