Mysql计数与条件或案例不同

SELECT user_appoint.TreatmentCaseId,
user_appoint.TreatmentCase,
tbl_rank.rank, 
user_appoint.u_id, 
DATE_FORMAT(MAX(user_appoint.ApptDateTime), ("%d-%m-%Y")) AS mdate, 
(CASE user_appoint.TreatmentCase 
WHEN 1 THEN "Open" WHEN 0 THEN "Closed" 
WHEN 2 THEN "Hospitalized" ELSE "" END) AS CaseStatus,
user_det.dob FROM (`user_appoint`) JOIN `user_det` ON 
`user_appoint`.`u_id` = `user_det`.`id` JOIN `tbl_rank` ON 
`user_appoint`.`rank` = `tbl_rank`.`rank_id` WHERE 
`user_appoint`.`comp_id` = '123' AND 
`user_appoint`.`void` = 0 AND 
`user_appoint`.`purpose` = 2 AND 
`TreatmentCaseId` LIKE '%%' 
GROUP BY 
`user_appoint`.`TreatmentCaseId` 
LIMIT 5

This is my query and i am getting the result as

这是我的查询,我得到的结果是

   TreatmentCaseId   TreatmentCase  mdate       CaseStatus    dob         

   A11                   2          10-03-2015  Hospitalized  1988-08-20  
   A12                   0          27-11-2014  Closed        1986-08-26  
   A13                   1          26-11-2014  Open          1988-08-20  
   A14                   1          25-11-2014  Open          1988-08-20

and now i want to count the number of casestatus i.e i will get open=2 , closed=1 , hospitalized=1 The date that is been select is the max date, so based on the max date i have selected the column and after that column is selected the number of open , closed and hospitalized cases will be counted.

现在我想计算casestatus的数量,即我将打开= 2,关闭= 1,住院= 1选择的日期是最大日期,因此根据我选择列的最大日期,之后栏目被选中开放,关闭和住院病例数将被计算在内。

2 个解决方案

#1

You can use conditional sum:

你可以使用条件总和:

select sum(CaseStatus = 'Open') as OpenedCount
     , sum(CaseStatus = 'Closed') as ClosedCount
     , sum(CaseStatus = 'Hospitalized') as HospitalizedCount
from (<...>)

#2

select count(*), CaseStatus from ("your previous query") group by CaseStatus;

通过CaseStatus从(“您之前的查询”)组中选择count(*),CaseStatus;

#1