CODEWITHSUNDEEP
sqlsql-server
Caroline
Caroline

Reputation: 64

A count of something inside an INNER JOIN?

Let's say I had a table of machines our employees use called machineManufacturer, which had the columns ID, and manufacturer. Then, I had another table that was added to whenever that machine was used called deviceUsage, which has the columns ID, and date.

I want to know the number of times used grouped according to manufacturer.

I believe the answer has something to do with COUNT and an INNER JOIN, but I am fairly new to SQL (only took one databases class in school).

I have tried something like:

SELECT manufacturer
FROM machineManufacturer
INNER JOIN deviceUsage
ON machineManufacturer.ID = deviceUsage.ID

which returns a big column of manufacturers, without any count. I am trying to count them and get a table like--

Manufacturer: Count
Dell: 30
HP: 27
Mac: 9
Lenovo: 14
etc.

Any comments welcome. Thank you for your time.

Upvotes: 0

Views: 26

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

I think you want group by:

SELECT MF.manufacturer, count(*)
FROM machineManufacturer MF INNER JOIN
     deviceUsage D
     ON MF.ID = D.ID
GROUP BY MF.manufacturer;

Upvotes: 1

Related Questions