MySQL在group by子句中计算多个列

本文介绍了MySQL在group by子句中计算多个列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想计算group by子句中使用的每个不同组。我现在正在使用此查询：

I'd like to count each distinct group being used in a group by clause. I am using this query right now:

SELECT language, group, count(*) AS frequency FROM bfs where firstname <> '' GROUP BY language, group

这给了我这样的结果：

'Language' 'Group' 'Frequency' 'ARABIC' 'LEBANESE' 1080 'ARABIC' 'MUSLIM' 40963 'ARABIC' 'MUSLIM MIDDLE EAST' 349 'ARABIC' 'MUSLIM NORTH AFRICAN' 549

我想要的是：而不是语言和组列的每个组合的总频率，我想知道每种语言和组属性有多少条记录

What I would like is: instead of total frequency of each combination of language and group column, I'd like to know how many records of each language and group attributes are there

'Language' 'Group' 'Frequency' 'Language Frequency' 'Group Frequency' 'ARABIC' 'LEBANESE' 1080 42941 1080

其中语言频率表示以ARABIC作为其值的总行数;类似地，组频率表示包含LEBANESE作为组频率的行的总数。 任何帮助将非常感激。

Where Language Frequency represents the total number of rows with 'ARABIC' as its value; similarly Group Frequency represents the total number of rows which contains 'LEBANESE' as the group frequency. Any help would be greatly appreciated.

谢谢！

推荐答案

SELECT g.language, g.`group`, g.frequency, gl.language_frequency, gg.group_frequency FROM ( SELECT language, `group`, COUNT(*) AS frequency FROM bfs WHERE firstname > '' GROUP BY language, `group` ) AS g JOIN ( SELECT language, COUNT(*) AS languageFrequency FROM bfs WHERE firstname > '' GROUP BY language ) AS gl ON gl.language = g.language JOIN ( SELECT `group`, COUNT(*) AS group_frequency FROM bfs WHERE firstname > '' GROUP BY `group` ) AS gg ON gg.`group`= g.`group` ;