MySQL: multiple counts with join

Question

Here are my two tables:

[items]
- id - model - location_id -

  1    mA      23
  2    mA      23
  3    mA      23
  4    mB      24
  5    mB      24
  6    mC      25
  7    mC      26


[locations]
- id - name -

  23   aisle-3
  24   aisle-4
  25   aisle-5
  26   aisle-6

I am trying to query the locations table for the location names and also bring back a count of the items at that location. Here is something I tried to no avail:

SELECT name, COUNT(item.id)
FROM locations
INNER JOIN items AS item ON (item.location_id = locations.id)

Can anyone help me with this?

Alex · Accepted Answer · 2015-10-27 14:41:14Z

1

You forgot to GROUP BY:

SELECT l.*, COUNT(item.id)
FROM locations l
INNER JOIN items AS i 
ON i.location_id = l.id
GROUP BY l.id

And if you want to get COUNT() even when there is no item assigned to that location you should LEFT JOIN instead of INNER JOIN.

answered Oct 27, 2015 at 14:41

Alex

17.3k1 gold badge31 silver badges52 bronze badges

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

Collectives™ on Stack Overflow

MySQL: multiple counts with join

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related