Saad Bashir Saad Bashir - 2 months ago 9
MySQL Question

MySQL - Use Count Without Where in Left Join

My table looks as follows:

Products

id title price
--------------------------------------
5 777 15
6 777 16
7 777 14


Product Details

id pid quantity color
--------------------------------------
1 5 5 red
2 5 5 yellow
3 5 5 green
4 6 6 yellow
5 6 5 red
6 7 5 red
7 7 4 pink


I want to get following output:

Desired Results

id title price numberofdetails quantity
------------------------------------------------------
5 777 15 3 15
6 777 16 2 11
7 777 14 2 9


In order to get this i am running following mysql query:

Query

SELECT p.id, p.title, p.price, COUNT(pd.id), SUM(pd.quantity)
FROM products as p
LEFT JOIN productdetails as pd
ON p.id = pd.pid


The wrote this query assuming that since I have defined the relationship between products.id and productdetails.pid it would COUNT(pd.id) = 3. Instead of counting only matching rows it shows '7', the total number of rows. How can I fix my query to get the desired results as show above.

Answer

You are pretty close. I think you just need GROUP BY:

SELECT p.id, p.title, p.price, COUNT(pd.id), SUM(pd.quantity)
FROM products p LEFT JOIN
     productdetails pd
     ON p.id = pd.pid
GROUP BY p.id, p.title, p.price;

In most databases, your query would return an error. However, MySQL extends the definition of SQL to allow columns in the SELECT (such as p.id) that are not in the GROUP BY. Because of the aggregation functions, you have an aggregation query. Without a GROUP BY, it always returns one rows, which summarizes all the data.