CODEWITHSUNDEEP
mysqlsql
Raj Parekh
Raj Parekh

Reputation: 94

Mysql count distinct with condition or case

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.

Upvotes: 0

Views: 174

Answers (2)

potashin
potashin

Reputation: 44581

You can use conditional sum:

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

Upvotes: 2

Swati Joshi
Swati Joshi

Reputation: 71

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

Upvotes: 1

Related Questions