Post reply

Query

  • November 13, 2013 at 9:32 am

    #300141

    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

    #1666134

    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!

  • November 13, 2013 at 9:47 am

    #1666136

    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

    #1666142

    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