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

Trainee SQL
Post #1513950
Posted Wednesday, November 13, 2013 9:42 AM



Group: General Forum Members
Last Login: Today @ 3:47 PM
Points: 13,883, Visits: 35,809
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:
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


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



Group: General Forum Members
Last Login: Tuesday, November 24, 2015 7:20 AM
Points: 492, Visits: 558
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



Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 6,327, Visits: 15,465
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.

I had some words with my wife, and she had some paragraphs with me.
Post #1513969
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse