November 13, 2013 at 9:32 am
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
November 13, 2013 at 9:42 am
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
November 13, 2013 at 9:47 am
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.
November 13, 2013 at 9:56 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply