开发者

How to COUNT column values differently in a VIEW?

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

上一篇:

下一篇:

精彩评论

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

最新问答

问答排行榜