CODEWITHSUNDEEP
mysql
enchance
enchance

Reputation: 30421

Query product list with quantity bought for each

My MySQL isn't as sharp as others and I've been trying to figure this out for the past hour. Maybe you can point me in the right direction. I have a list of products and I'm trying to get a report out of them. This is what I have:

-- Product list: tbl_products
id       product_name
------  ----------------
1        Hamburger
2        Cheeseburger
3        Burger Balls


-- List of orders: tbl_orders
id       created
------  -----------------------
23       2013-09-04 00:00:00
45       2013-09-05 00:00:00
67       2013-09-09 00:00:00


-- Products bought per order: tbl_product_orders
order_id    product_id      quantity
----------  --------------  --------
23          1                4
23          2                6
45          1                1
67          3                2

This is what I'm trying to achieve with one query:

-- How do I get this
order_id    Hamburger     Cheeseburger    Burger Balls
----------  ------------- --------------- --------------
23           4             6               0
45           1             0               0
67           0             0               2

Does anyone know how to do this?

Edit: I'll post my earlier scripts in a bit. I just have to run to class right now. :)

Upvotes: 1

Views: 222

Answers (2)

AdrianBR
AdrianBR

Reputation: 2588

this will work, only one join:

select order_id,
sum(if(product_orders.product_id=1, product_orders.quantity, 0)) as hamburglars,
sum(if(product_orders.product_id=2, product_orders.quantity, 0)) as cheese_burglars,
sum(if(product_orders.product_id=3, product_orders.quantity, 0)) as burglar_balls
from orders inner join product_orders on orders.id=product_orders.order_id
group by order_id

Upvotes: 1

peterm
peterm

Reputation: 92795

You can do it with dynamic SQL

SET @sql = NULL;

SELECT GROUP_CONCAT(CONCAT('SUM(CASE WHEN product_id = ', id,
                           ' THEN quantity ELSE 0 END) `', product_name, '`'))
  INTO @sql
  FROM tbl_products;

SET @sql = CONCAT('SELECT order_id,', @sql,
                  '  FROM tbl_product_orders
                    GROUP BY order_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Output:

| ORDER_ID | HAMBURGER | CHEESEBURGER | BURGER BALLS |
|----------|-----------|--------------|--------------|
|       23 |         4 |            6 |            0 |
|       45 |         1 |            0 |            0 |
|       67 |         0 |            0 |            2 |

Here is SQLFiddle demo

To simplify things on calling end you can wrap it into a stored procedure

DELIMITER $$
CREATE PROCEDURE sp_report()
BEGIN
  SET @sql = NULL;

  SELECT GROUP_CONCAT(CONCAT('SUM(CASE WHEN product_id = ', id,
                             ' THEN quantity ELSE 0 END) `', product_name, '`'))
    INTO @sql
    FROM tbl_products;

  SET @sql = CONCAT('SELECT order_id,', @sql,
                    '  FROM tbl_product_orders
                      GROUP BY order_id');

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

Here is SQLFiddle demo

Upvotes: 5

Related Questions