MySQL SELECT QUERY same column different where statements

Question

Currently I have 2 tables. Service 1 name and Service info.

I need to grab all Unique names from service name and show how many service infos are attached to it eg.

SELECT cs.serviceDesc as 'SERVICE', COUNT(cr.serviceId) as 'Count',
FROM consultationRequests cr
JOIN consultationServices cs on cs.serviceId = cr.serviceId 
JOIN demographic d on d.demographic_no = cr.demographicNo 
WHERE cs.active = 1  
AND cr.referalDate between '2018-11-31' and '2019-01-20' 
GROUP BY cr.serviceId

There is also another column in consultationRequests that has a status number between 1-4. I need to add in another column for each status and count the number of infos that are within that status WITHIN the service name.

update your question adda proper data sample and the expected result — ScaisEdge, Jan 25 '19 at 21:02
I would think that you will need to group by on both columns, then you can get the information that you need. — dmoore1181, Jan 25 '19 at 21:06

score 0 · Accepted Answer · answered Jan 25 '19 at 21:06

You can use conditional aggregation:

SELECT cs.serviceDesc as SERVICE,
       COUNT(cr.serviceId) as Count,
       SUM(cr.consultationRequests = 1) as cnt_1,
       SUM(cr.consultationRequests = 2) as cnt_2,
       SUM(cr.consultationRequests = 3) as cnt_3,
       SUM(cr.consultationRequests = 4) as cnt_4
FROM consultationRequests cr JOIN
     consultationServices cs
     ON cs.serviceId = cr.serviceId JOIN
     demographic d
     ON d.demographic_no = cr.demographicNo 
WHERE cs.active = 1  AND
      cr.referalDate between '2018-11-31' and '2019-01-20' 
GROUP BY cr.serviceId

MySQL SELECT QUERY same column different where statements

1 Answers1