如何获取同时具有null和非nnull值的列的计数 [英] How to take the count of columns with both null and not nnull value

问题描述

嗨Firends


我有一个带有值的表


ID名称日期状态

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


我想根据日期将状态为null的列计数为NotVerified，将状态为notnull的列计数为已验证

o/p就是这样

未验证已验证
1 2

如何编写查询



问候
Chinnu

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

推荐答案

尝试一下:

Try this:

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

Chinnu ..

我认为以下代码块将对您有所帮助

Hi Chinnu..

I think following code block will help you

 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

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!
:)

这篇关于如何获取同时具有null和非nnull值的列的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！