mysql按3个字段分组，带计数

hivapdat 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(340)

请参阅所附的图片，以查看我的数据。
我试图得到这样的结果：

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

我正在计算各种主要的程序集、主题、每个sessionnumber的reg和eventdate。
这是我正在使用的查询：

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

但我宁愿不用where子句。我想就这个词本身进行分组。以下是屏幕截图：

sql mysql etl domo

来源：https://stackoverflow.com/questions/50398843/mysql-group-by-3-fields-with-count

1条答案

按热度按时间

xfyts7mz1#

数据透视查询有助于：

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

赞(0）回复(0）举报 2021-06-21

我来回答

mysql按3个字段分组，带计数

1条答案

相关问题

热门标签

最新问答