CODEWITHSUNDEEP
mysqljoincount
ThriceGood
ThriceGood

Reputation: 1703

MySQL: multiple counts with join

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?

Upvotes: 1

Views: 59

Answers (1)

Alex
Alex

Reputation: 17289

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.

Upvotes: 1

Related Questions