CODEWITHSUNDEEP
sqloracle-database
bobtastic
bobtastic

Reputation: 159

Trying to count across two tables

I have two tables like so:

Resources

ID | Resource
1  | 1234
2  | 1356
3  | 1456

Products

ID | Product
1  | North
1  | South
1  | West
2  | North
3  | East
3  | West

I'd like to achieve a count of the number of Products each Resource is used on so that the result is:

Resource | Products
1234     | 3
1356     | 1
1456     | 2

The SQL I have written is:

SELECT R.Resource, count (P.Product) as Products
from Resource R
left join Product P on R.ID = P.ID
GROUP BY R.Resource, P.Product;

But this is giving the following result

Resource | Product
1234     | 1
1234     | 1
1234     | 1
1356     | 1
1456     | 1
1456     | 1

Can someone please point me in the right direction please?

Upvotes: 1

Views: 21

Answers (1)

Matt
Matt

Reputation: 15071

Use a COUNT and GROUP BY as you did but with an INNER JOIN.

SELECT r."Resource", COUNT(p."Product") AS Products
FROM Resources r
INNER JOIN Products p ON r."ID" = p."ID"
GROUP BY r."Resource"

Output

Resource PRODUCTS
1356     1
1456     2
1234     3

SQL Fiddle: http://sqlfiddle.com/#!4/7c01a/27/0

Upvotes: 1

Related Questions