MySQL counting multiple columns in a group by article

Question

I would like to consider each individual group used in the group by section. I am using this query right now:

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

Which gives me the result as follows:

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

I would like: instead of the total frequency of each combination of a language column and a group, I would like to know how many records of each attribute of the language and group are

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

In cases where Language Frequency represents the total number of lines with "ARABIC" as the value; Similarly, Group Frequency represents the total number of rows that contains “LEBANESE” as the group frequency. Any help would be greatly appreciated.

Thank!

Answer 1

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