Mysql Group by 3 fields with count

Question

See the attached image to see the data I have.

I am trying to get a result like this:

SessionNumber | Event Date| Critical Care Count | Pulmonary Circulation
G1            | 5/19/2018 | 2                   | 3
G1            | 5/20/2018 | 5                   | 1
PCC1          | 5/19/2018 | 4                   | 5

I'm trying to count the various primaryAssembly, topic, reg per SessionNumber and EventDate.

This is the query I am using:

select SessionNumber, EventDate, count(distinct BadgeID) as CriticalCareCount 
from beacon 
where primaryAssembly="Critical Care" 
group by SessionNumber, EventDate 
order by EventDate;

But I would rather not have to use the 'Where' clause. I'd like grouping on the term itself. Here's a screen shot:

Edit - realized Pivot is MSSQL specific - I think case statements will have to do - https://stackoverflow.com/a/12005676/1313067 for guidance. — Hatt, May 17 '18 at 19:12

krokodilko · Accepted Answer · 2018-05-17T19:28:36.993

A pivot query can help:

SELECT SessionNumber,Event_Date,
       count( case when primaryAssembly = 'Critical Care' then 1 end ) 
                   As Critical_Care_Count,
       count( case when primaryAssembly = 'Pulmonary Circulation' then 1 end ) 
                   As Pulmonary_Circulation_Count,
       count( case when primaryAssembly = 'Some other value' then 1 end ) 
                   As Some_other_value_Count,
       ......
       ......
       count( case when some_other_logical_condition then 1 end ) 
                   As some_other_condition_count
       ......
       ......
       SUM( case when primaryAssembly = 'Critical Care' then Duration else 0 end ) 
                   As sum_of_duration_for_critical_care
       ......
       ......
       count(*) As total_count
FROM table
GROUP BY SessionNumber,Event_Date

if I want percentage, would it be wise to do count( case when primaryAssembly = 'Critical Care' then 1 end ) /count(*) ?? — , May 17 '18 at 20:32

Mysql Group by 3 fields with count

1 Answers1