开发者

How to COUNT column values differently in a VIEW?

开发者 https://www.devze.com 2023-01-15 11:29 出处:网络
I have a column in Datablase Table, Suppose its Observation which contains three types of values Positive

I have a column in Datablase Table, Suppose its Observation which contains three types of values

  1. Positive
  2. Negative
  3. NULL

Now I want to count the Total no of rows , Total Positive and Total Negative and some other columns. I can not use Where clause here. And its a view

So result should be like

Total   Pos开发者_C百科itive   Negative   SomeOtherCoulumn
 255       80        120            Test1
 315      135         65            Test2  

I tried to use SQL COUNT here but could not get the desired results.


SELECT
    COUNT(*) AS Total,
    SUM(CASE WHEN Observation = 'Positive' THEN 1 ELSE 0 END) AS Positive,
    SUM(CASE WHEN Observation = 'Negative' THEN 1 ELSE 0 END) AS Negative,
    SomeOtherColumn
FROM your_view
GROUP BY SomeOtherColumn


There's an interesting technique of summing a case expression like so:

sum(case when Observation = 'Positive' then 1 else 0 end) 'TotalPositive' 

The rest is easy.

0

精彩评论

暂无评论...
验证码 换一张
取 消