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
I believe you’re looking for the following: FROM tbl, SELECT a, b, COUNT(a) GROUP BY a, b
Answered by Corbin
SELECT group,subGroup,COUNT(*) FROM tablename GROUP BY group,subgroup
Answered by user1127214
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