How to take the count of columns with both null and not nnull value

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi Firends

I'm having a table with values

Id Name Date Status

1 A 2012-10-27 1
2 B 2012-10-27 1
3 C 2012-10-28 null
4 D 2012-10-27 null

i want to take the count of columns with status null as NotVerified and status with notnull as Verified based on date

o/p is like this

NotVerified Verified
1 2

How to write the query

Regards
Chinnu

Posted 28-Oct-12 0:21am

chinnu11

Add a Solution

Comments

Sandeep Mewara 28-Oct-12 9:09am

Not clear.
i want to take the count of columns with status null as NotVerified and status with notnull as Verified based on date
Elaborate.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kuthuparakkal · Answer 1 · 2012-10-28T04:22:00

Solution 1

Try this:

SQL

SELECT 
  SUM(Verified) AS Verified
 ,SUM(NotVerified) AS NotVerified
  SELECT
     NotVerified = CASE WHEN Status IS NULL THEN 1 ELSE 0 END
   , Verified    = CASE WHEN Status IS NOT NULL THEN 1 ELSE 0 END
  FROM MyTable
)AS MyStatus

Posted 28-Oct-12 4:22am

Kuthuparakkal

damodara naidu betha · Answer 2 · 2012-10-28T21:05:00

Hi Chinnu..

I think following code block will help you

SQL

 WITH DemoCTE AS (
 SELECT [Status],[Date],ROW_NUMBER() OVER(Partition By [Date],
 [Status] ORDER BY [Date]) RowNumber FROM YourTable
 )

SELECT [Date], ISNULL((SELECT MAX(RowNumber) FROM DemoCTE WHERE [Status] IS NOT NULL AND [Date] = D.Date),0) As Verified,ISNULL((SELECT MAX(RowNumber) FROM DemoCTE WHERE [Status] IS NULL AND [Date] = D.Date ),0) NotVerified
FROM DemoCTE D GROUP BY [Date]

Thank you

Aarti Meswania · Answer 3 · 2012-10-28T21:26:00

Solution 3

SQL

select date, sum(NotVarified) as NotVarified, sum(Varified) as Varified from
(
    select
        date,
        case when status is null 1 else 0 end as NotVarified,
        case when status is null 0 else 1 end as Varified
    from tbl
)as temptbl
group by date

Happy Coding!
:)

Posted 28-Oct-12 21:26pm

Aarti Meswania