Say you have a table such as:
id status date
-----------------------
1 2 today
2 3 today
3 3开发者_StackOverflow yesterday
4 2 yesterday
5 1 yesterday
And you want a query that counts the number or results that have the status 1, 2 or 3 for a given date, in the example the desired result set would be:
date status 1 status 2 status 3
count count count
------------------------------------------
today 0 1 1
yesterday 1 1 1
Can anyone point me in the right direction?. Thank you.
Select Date
, Sum( Case When Status = 1 Then 1 Else 0 End ) Status1Count
, Sum( Case When Status = 2 Then 1 Else 0 End ) Status2Count
, Sum( Case When Status = 3 Then 1 Else 0 End ) Status3Count
From MyTable
Group By Date
select status, date, count(*)
from Table
group by status, date
result:
status date count
2 today 1
3 today 1
3 yesterday 1
2 yesterday 1
1 yesterday 1
精彩评论