Menu
I have a table for "branches", "orders" and "products. Each order and product are connected to a branch with branch_id. I need an sql statement to get a list of all branches with a field for how many orders and a field for how many products.
This works:
SELECT b.*, COUNT(o.id) AS orderCount FROM branches b
LEFT JOIN orders o ON (o.branch_id = b.id) GROUP BY b.id
but it only gets the amount of orders, not products. If I change it to add amount of products, the amounts are wrong because it's getting amount of orders * amount of products.
How can I get the amount of both the orders and the products in the same SQL statement?
702
Learn MySQL from scratch for Data Science and Analytics You can count multiple COUNT() for multiple conditions in a single query using GROUP BY. SELECT yourColumnName,COUNT(*) from yourTableName group by yourColumnName; To understand the above syntax, let us first create a table.
The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
Using COUNT, without GROUP BY clause will return a total count of a number of rows present in the table. Adding GROUP BY, we can COUNT total occurrences for each unique value present in the column. we can use the following command to create a database called geeks.
Something like this should work (on sql server at least - you didn't specify your engine).
SELECT
b.id
,COUNT(distinct o.id) AS orderCount
,COUNT(distinct p.id) AS productCount
FROM branches b
LEFT JOIN orders o
ON (o.branch_id = b.id)
left join products p
on p.product_id=b.id)
GROUP BY
b.id
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
