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

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

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

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

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

其中，Language Frequency表示以“ARABIC”为值的行总数；同样，Group Frequency表示以“黎巴嫩”为组频率的行总数。
任何帮助都将不胜感激。
谢谢！

最佳答案

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` ;

关于mysql - MySQL在group by子句中计算多个列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/17065325/