Using group by on two fields and count in SQL

Question

I have a table in my mysql db that has two columns: group and subgroup. See 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 am trying to get the number of records for each unique couple group/subGroup.

This is what I expect:

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?

i wanted another case, count of sugroups for each group so this is the query SELECT a, b, COUNT(distinct b) FROM tbl GROUP BY a — luky
– luky, Commented May 18, 2019 at 12:58

Corbin · Accepted Answer · 2012-04-30 09:05:38Z

179

I think you're looking for: SELECT a, b, COUNT(a) FROM tbl GROUP BY a, b

answered Apr 30, 2012 at 9:05

Corbin

33.6k7 gold badges73 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Marc Over a year ago

Hello Corbinb. Thank you very much. it is working. I did what you are suggesting but insted of group by a, b I did group by a and b. Thanks again

Corbin Over a year ago

a and b looks like a statement to MySQL (not actually sure if it would be a syntax error or if it would group on the 0 or 1 return -- I would assume a syntax error, especially for varchar fields). To group on multiple columns, you just use a comma.

faheem farhan Over a year ago

Thanks a lot!! .. The issue with me was I wasn't able to phrase my question and spent a lot of time googling finally landed here for a simple and dot solution.

admin 7798 Over a year ago

Instead of the count I needed the entire record details. For example, grpA has to 2 subgroups: subA and subB, so I need to retrieve the entire record for each of the subgroups. Can anyone please help me. @Corbin

luky Over a year ago

i wanted count of sugroups for each group so this is the query SELECT a, b, COUNT(distinct b) FROM tbl GROUP BY a

nawfal · Accepted Answer · 2012-04-30 10:57:53Z

11

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

edited Apr 30, 2012 at 10:57

nawfal

73.7k59 gold badges342 silver badges379 bronze badges

answered Apr 30, 2012 at 9:07

user1127214

3,1777 gold badges28 silver badges30 bronze badges

1 Comment

admin 7798 Over a year ago

Instead of the count I needed the entire record details. For example, grpA has to 2 subgroups: subA and subB, so I need to retrieve the entire record for each of the subgroups. Can anyone please help me. @user1127214

Minding · Accepted Answer · 2016-03-04 18:21:32Z

8

You must group both columns, group and sub-group, then use the aggregate function COUNT().

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

edited Mar 4, 2016 at 18:21

Minding

1,4331 gold badge21 silver badges30 bronze badges

answered May 16, 2013 at 8:58

farzane

891 silver badge1 bronze badge

2 Comments

csharpwinphonexaml Over a year ago

Seems a typo to me group!= groupName

admin 7798 Over a year ago

Instead of the count I needed the entire record details. For example, grpA has to 2 subgroups: subA and subB, so I need to retrieve the entire record for each of the subgroups. Can anyone please help me. @farzane

Pradeep M Nayak · Accepted Answer · 2023-02-16 09:37:41Z

0

It is easily possible. You need to GROUP BY multi fields. Those are on 'mainGroup' and then 'subGroup'

select mainGroup, subGroup, Count(mainGroup) from table group by mainGroup, subGroup

answered Feb 16, 2023 at 9:37

Pradeep M Nayak

582 silver badges9 bronze badges

Collectives™ on Stack Overflow

Using group by on two fields and count in SQL

4 Answers 4

5 Comments

1 Comment

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

1 Comment

2 Comments

Comments

Linked

Related