MySQL在group by子句中计算多个列 [英] MySQL counting multiple columns in group by clause

问题描述

我想计算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` ;

这篇关于MySQL在group by子句中计算多个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！