[SOLVED] MySQL - COUNT for rows with the same name

MySQL - COUNT for rows with the same name

I want to count how many entries with the same name from my table device [device.name] have the value 2 as warehouse [device.warehouse_id].

So for example I have 5 rows, 2 with name = Beam, 2 with name = Spot and 1 with name = Strobe.

And now I want the result from the select to be:

2 Beam

2 Spot

1 Strobe

(want to know the stock of every device)

I know it has to be something with "SELECT COUNT(device.name) FROM device WHERE device.warehouse_id = '2'

That would work, and the COUNT itself works too, but I want to count every "set" of names.

Of course I could do an UNION and write the same query for each device.name, but I want to know how I can do it for all existing names at once, without to write them down.

I hope you know what I mean and what I want.

Thanks, Fabio

Solution

Try

SELECT 
  COUNT(*), device.name
FROM device 
WHERE 
  device.warehouse_id = '2' 
GROUP BY 
  device.name