开发者

SQL reports how to count number of records that match a certain criteria

开发者 https://www.devze.com 2023-02-21 18:50 出处:网络
Say you have a table such as: idstatusdate ----------------------- 12today 23today 33开发者_StackOverflowyesterday

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
0

精彩评论

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