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](http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks) Your column `GROUP NAME` should _not_ be single quoted. Rather it should be quoted with backticks. — Michael Berkowski, Feb 21 '14 at 03:08

score 2 · Accepted Answer · answered Feb 21 '14 at 03:12

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 '14 at 03:12

Michael Berkowski

267,341
46
444
390

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'` – Feb 21 '14 at 03:19
1

@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](http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins/44932#44932) – Michael Berkowski Feb 21 '14 at 04:30

score 1 · Answer 2 · answered Feb 21 '14 at 03:14

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

MySQL Two Table Query Count

2 Answers2