Get quantity per product in each order using MySQL

Question

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 |
-------------------------------------------

you are already getting in the same data in third table..show how are you searching user name, also user table structure — lakshman, Aug 07 '15 at 10:17
@DevLakshman The user table is the `orders` table at least in the example data. But I also think there are missing information or at least the example data is already summed up — bish, Aug 07 '15 at 10:21

score 1 · Accepted Answer · answered Aug 07 '15 at 11:19

1

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().

answered Aug 07 '15 at 11:19

Gordon Linoff

1,242,037
58
646
786

This is great. I'm using this query right now. I made a follow-up question to this which uses columns instead of rows. Do you mind taking a look at it as well? It's in [MYSQL: Separate products into columns](http://stackoverflow.com/questions/31910164/mysql-separate-products-into-columns). – enchance Aug 10 '15 at 00:35

score 0 · Answer 2 · answered Aug 07 '15 at 10:28

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.

Get quantity per product in each order using MySQL

2 Answers2

Linked