MySQL Two Table Query Count

Question

I have two tables A & B.

Table A

GROUP #  |  GROUP NAME
   1         group a
   2         group b 
   3         group c

Table B will have a column with group # and then a bunch of other data.

I'm trying to figure out how I can create a query that will return the GROUP NAME from TABLE A and the number of rows associated with each group name from TABLE B.

Essentially I'm trying to get the following output:

GROUP NAME | COUNT
  group a      x
  group b      y
  group c      z

I've been trying variations of the following to no avail...

SELECT DISTINCT "GROUP NAME", COUNT(*) from A, B where A.GROUPNUM = B.GROUPNUM;

See When to use single quotes, double quotes, backticks Your column GROUP NAME should not be single quoted. Rather it should be quoted with backticks. — Michael Berkowski
– Michael Berkowski, Commented Feb 21, 2014 at 3:08

Michael Berkowski · Accepted Answer · 2014-02-21 03:12:05Z

2

You will need to apply the aggregate COUNT() over the group GROUP NAME (using GROUP BY) to get the count you are looking for. DISTINCT doesn't come into play in this instance,

Note that a column name with spaces will need to be quoted using backticks, not single or double quotes. I'm also replacing your implicit join with an explicit INNER JOIN, which is the modern preferred syntax.

SELECT
  `GROUP NAME`,
  COUNT(B.GROUPNUM)
FROM
  A
  INNER JOIN B ON A.GROUPNUM = B.GROUPNUM
GROUP BY `GROUP NAME`

Here's a little demonstration: http://sqlfiddle.com/#!2/2123f/2

answered Feb 21, 2014 at 3:12

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

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

2 Comments

user1527185 Over a year ago

Thank you very much for the help. However, is there any performance difference (or just preference) to using your code over the following code: SELECT 'GROUP NAME', COUNT(B.GROUPNUM) FROM A, B WHERE A.GROUPNUM = B.GROUPNUM GROUP BY 'GROUP NAME'

Michael Berkowski Over a year ago

@Kevin It isn't an advantage so much as just a more modern syntax. The old syntax is still supported by all RDBMS, but was replaced in the early 1990s by the explicit INNER/OUTER JOIN Some RDBMS don't support left/right joins via the implicit style. Performance wise, they should be the same

Brian DeMilia · Accepted Answer · 2014-02-21 03:14:27Z

1

Try the below, note that you will want to use a left join (like below) if you also want to show group names on table a that have no rows on table b.

select a.group_name, count(b.groupnum)
  from a
  left join b
    on a.groupnum = b.groupnum
 group by a.group_name

answered Feb 21, 2014 at 3:14

Brian DeMilia

13.3k1 gold badge25 silver badges36 bronze badges

Collectives™ on Stack Overflow

MySQL Two Table Query Count

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related