Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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 @ 6:24 PM
Points: 14,469, Visits: 38,081
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!
Post #1513958
Posted Wednesday, November 13, 2013 9:47 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, May 17, 2016 8:32 AM
Points: 552, Visits: 580
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 @ 2:25 PM
Points: 7,577, Visits: 18,325
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.

The Dunning-Kruger Effect: the metacognitive inability of those of low ability to recognize their ineptitude and evaluate their ability accurately.
Post #1513969
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse