CODEWITHSUNDEEP
mysql
enchance
enchance

Reputation: 30421

Get quantity per product in each order using MySQL

I have 3 tables, order, products, and orders_products and I'm trying to find out how many of each product was bought in each order. Is it possible to get all information in a single query?

My current query doesn't seem to work, whereas the product_id comes out as all the same.

SELECT orders.order_id, orders.username, COUNT( DISTINCT op.product_id ) product_id, SUM( op.quantity ) quantity
FROM  `orders` 
JOIN orders_products op ON op.order_id = orders.order_id
GROUP BY product_id, orders.order_id ORDER BY order_id, product_id

Which results in:

_______________________________________________
| order_id | username | product_id | quantity |
-----------------------------------------------
|        1 | bill     |          1 |        3 |
-----------------------------------------------
|        1 | bill     |          1 |        2 |
-----------------------------------------------
|        1 | bill     |          1 |        5 |
-----------------------------------------------
|        2 | sally    |          1 |        2 |
-----------------------------------------------
|        3 | jeff     |          1 |        6 |
-----------------------------------------------
|        3 | jeff     |          1 |        7 |
-----------------------------------------------

You can see the problem above in the product_id column which is always set to 1.

I'm trying to get something like this:

_______________________________________________
| order_id | username | product_id | quantity |
-----------------------------------------------
|        1 | bill     |          1 |        5 |
-----------------------------------------------
|        1 | bill     |          2 |        3 |
-----------------------------------------------
|        1 | bill     |          3 |        2 |
-----------------------------------------------
|        2 | sally    |          1 |        2 |
-----------------------------------------------
|        3 | jeff     |          1 |        6 |
-----------------------------------------------
|        3 | jeff     |          2 |        7 |
-----------------------------------------------

My tables:

-- Orders
_______________________
| order_id | username |
-----------------------
|        1 | bill     |
-----------------------
|        2 | sally    |
-----------------------
|        3 | jeff     |
-----------------------

-- Products
___________________
|    id | product |
-------------------
|     1 | Table   |
-------------------
|     2 | Chair   |
-------------------
|     3 | Mouse   |
-------------------

-- Order Products
___________________________________________
|   id | order_id | product_id | quantity |
-------------------------------------------
|    1 |        1 |          1 |        5 |
-------------------------------------------
|    2 |        1 |          2 |        3 |
-------------------------------------------
|    3 |        1 |          3 |        2 |
-------------------------------------------
|    4 |        2 |          1 |        2 |
-------------------------------------------
|    5 |        3 |          1 |        6 |
-------------------------------------------
|    6 |        3 |          2 |        7 |
-------------------------------------------

Upvotes: 0

Views: 2250

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

I think the query you want is:

SELECT o.order_id, o.username, op.product_id, SUM( op.quantity ) as quantity
FROM  `orders` o JOIN 
      orders_products op
      ON op.order_id = o.order_id
GROUP BY op.product_id, o.order_id
ORDER BY o.order_id, op.product_id;

In general, you do want all the columns in the GROUP BY in the SELECT. But (in general) they shouldn't be arguments to aggregation functions such as COUNT().

Upvotes: 1

Rahul
Rahul

Reputation: 77876

You can see the problem above in the product_id column which is always set to 1

I think you are bit confused here, that's the data present in Order Products table for product_id column but in your query what you are trying to get is count of data COUNT( DISTINCT op.product_id ) product_id and since the count is 1, the result is showing the same.

Upvotes: 0

Related Questions