CODEWITHSUNDEEP
sqlmysql
Emanuil Rusev
Emanuil Rusev

Reputation: 35235

Get all entries from one table together with the count of these entries in another table

I've got 3 tables:

How do I get all users together with the number of downloads they have?

Upvotes: 1

Views: 183

Answers (2)

wallyk
wallyk

Reputation: 57774

This should do it:

SELECT u.name as name, count(*) as downloads
FROM users u, items i, downloads d
WHERE u.id = i.user_id AND i.user_id = d.user_id;

I had to invent items.user_id, but it should be available.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332541

How do I get all users together with the number of downloads they have?

Use:

SELECT u.name,
       COUNT(d.user_id) 'num_downloaded'
FROM USERS u
OIN DOWNLOADS d ON d.user_id = u.user_id
GROUP BY u.name

If you want to see how many times a user has downloaded a specific item:

SELECT u.name 'user_name',
       i.name 'item_name',
       COUNT(d.user_id) 'num_downloaded'
FROM USERS u
JOIN DOWNLOADS d ON d.user_id = u.user_id
JOIN ITEMS i ON i.item_id = d.item_id
GROUP BY u.name, i.name

Upvotes: 3

Related Questions