Coder Perfect

In SQL, using group by on two fields and count

Category: sql

Tag:

Problem

In my MySQL database, I have a table with two columns: group and subgroup. See the list below.

 group, subGroup
 grp-A, sub-A
 grp-A, sub-A
 grp-A, sub-B      
 grp-B, sub-A
 grp-B, sub-B
 grp-B, sub-B

I’m trying to figure out how many records there are for each unique marriage group/subGroup.

This is what I anticipate:

group, subGroup, count
grp-A, sub-A, 2
grp-A, sub-B, 1
grp-B, sub-A, 1
grp-B, sub-B, 2

After reading some posts I tried several sql queries using group by, count(), but I do not manage to get the expected result. How can I fix this?

Asked by Marc

Solution #1

I believe you’re looking for the following: FROM tbl, SELECT a, b, COUNT(a) GROUP BY a, b

Answered by Corbin

Solution #2

SELECT group,subGroup,COUNT(*) FROM tablename GROUP BY group,subgroup

Answered by user1127214

Solution #3

You must first group both columns, then group and sub-group, before using the COUNT aggregate method ().

SELECT
  group, subgroup, COUNT(*)
FROM
  groups
GROUP BY
  group, subgroup

Answered by farzane

Post is based on https://stackoverflow.com/questions/10380990/using-group-by-on-two-fields-and-count-in-sql