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
- Positive
- Negative
- 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.
精彩评论