thanks in advance for any help on this, I am a bit of a newbie to MS SQL and I want to do something that I think is achievable but don't have the know how.
I have a simple table called "suppliers" where I can do (SELECT id, name FROM suppliers ORDER BY id ASC)
| id | name |
|---|---|
| 1 | ACME |
| 2 | First Stop Business Supplies |
| 3 | All in One Supply Warehouse |
| 4 | Farm First Supplies |
I have another table called "products"
| id | name | supplier_id |
|---|---|---|
| 1 | Item 1 | 2 |
| 2 | Item 2 | 1 |
| 3 | Item 3 | 1 |
| 4 | Item 4 | 3 |
| 5 | Item 5 | 2 |
I want to list all the suppliers and get the total amount of products for each supplier if that makes sense on the same row? I am just not sure how to pass the suppliers.id through the query to get the count.
I am hoping to get to this:
| id | name | total_products |
|---|---|---|
| 1 | ACME | 2 |
| 2 | First Stop Business Supplies | 2 |
| 3 | All in One Supply Warehouse | 1 |
| 4 | Farm First Supplies | 0 |
I really appreciate any help on this.
CodePudding user response：
Three concepts to grasp here. Left Join, group by, and Count().
select s.id, s.name, Count(*) as total_products
from suppliers s
left join products p on s.id=p.supplier_id --the left join gets your no matches
group by s.id, s.name
left join is a join where all of the values from the first table are kept even if there are no matches in the second.
Group by is an aggregation tool where the columns to be aggregated are entered.
Count() is simply a count of transactions for the grouped columns.
CodePudding user response：
Try this :-
SELECT SELECT id, name, C.total_products
FROM Suppliers S
OUTER APPLY (
SELECT Count(id) AS total_products
FROM Products P
WHERE P.supplier_id = S.id
) C