CODEWITHSUNDEEP
mysql
Ryan
Ryan

Reputation: 35

How to show total cost across all orders per customer? (subquery)

I want to show the total cost of their order and show each item they bought as well. When I sort by order number the sum(price) gets messed up. And when I sort by customer name it only shows one product. How do I show all the products they purchased and total cost for those products. *Each order has one product attached to it. With a unique order number

These are fictions tables I created that closely resembles my own.

Customer Table
+------+-------------+
| name | customer_id |
+------+-------------+
| Adam |           4 |
| Drew |           6 |
| Tom  |           8 |
+------+-------------+

Order Table
+---------------+---------------+----------+--------------+
| purchase_date | delivery_date | order_id | customer_id  |
+---------------+---------------+----------+--------------+
| 01/22/2020    | 02/23/2020    |        3 |             4|
| 01/12/2020    | 02/12/2020    |        5 |             4|
| 01/31/2020    | 02/22/2020    |        6 |             4|
| 01/05/2020    | 02/14/2020    |       11 |             8|
| 01/22/2020    | 02/23/2020    |        7 |             8|
| 01/12/2020    | 02/12/2020    |        8 |             6|
| 01/31/2020    | 02/22/2020    |        9 |             6|
| 01/05/2020    | 02/14/2020    |       10 |             6|
| 01/05/2020    | 02/14/2020    |       10 |             6|
+---------------+---------------+----------+--------------+

Product_order table
+-------+------------+----------+
| po_id | product_id | order_id |
+-------+------------+----------+
|     1 |          3 |         3|
|     2 |         13 |         5|
|     3 |          7 |         6|
|     4 |          8 |        11|
|     5 |         45 |         7|
|     6 |          9 |         8|
|     7 |          3 |         9|
|     8 |         45 |        10|
|     9 |          3 |        10|
+-------+------------+----------+

product table
+------------+------------+-------+
| product_id |  product   | Price |
+------------+------------+-------+
|          3 | Soda       | 7.00  |
|         13 | Chips      | 9.00  |
|          7 | GummyBears | 11.00 |
|          8 | IceCream   | 3.00  |
|          9 | Pen        | 2.00  |
|         45 | Gum        | 1.00  |
+------------+------------+-------+

This is the query I tried but produced the result below.

select c.name, product, sum(p.price) as total_sale
from (Select distinct order_id, customer_id
    from `order`) as o
inner join (select o.customer_id
    from `order` o
    group by o.customer_id) as a on o.customer_id = a.customer_id
inner join product_order po on o.order_id = po.order_id
inner join customer c on o.customer_id = c.customer_id
inner join products p on po.product_id = p.product_id
group by c.name

this is the result:

+----------+------------+-----------------+
|   Name   |  product   | total_purchased |
+----------+------------+-----------------+
| Adam     | Soda       |            27.00|
| Tom      | IceCream   |             4.00|
| Drew     | Pen        |            17.00|
+----------+------------+-----------------+

I want to show all the products they bought. And the total they purchased all the items for, like so.

+------+------------+-----------------+
| Name |  product   | total_purchased |
+------+------------+-----------------+
| Adam | Soda       |            27.00|
| Adam | Chips      |            27.00|
| Adam | GummyBears |            27.00|
| Tom  | IceCream   |             4.00|
| Tom  | Gum        |             4.00|
| Drew | Pen        |            17.00|
| Drew | Soda       |            17.00|
| Drew | Gum        |            17.00|
| Drew | Soda       |            17.00|
+------+------------+-----------------+

Upvotes: 1

Views: 71

Answers (1)

nbk
nbk

Reputation: 49395

The data don't fit with the wanted result, but that is only a small problem

Order is a reserved word in mysql, so please try not to use them at all

The dates in your example are also not mysql standard, so i had to rewrite them

It is as i said, you have to calculate the total sum before joining it to the colplte Query

CREATE TABLE Customer  (
  `name` VARCHAR(4),
  `customer_id` INTEGER
);

INSERT INTO Customer 
  (`name`, `customer_id`)
VALUES
  ('Adam', '4'),
  ('Drew', '6'),
  ('Tom', '8');

CREATE TABLE `Order`  (
  `purchase_date` DATETIME,
  `delivery_date` DATETIME,
  `order_id` INTEGER,
  `customer_id` INTEGER
);

INSERT INTO `Order`
  (`purchase_date`, `delivery_date`, `order_id`, `customer_id`)
VALUES
  ('2020-01-22', '2020-02-23', '3', '4'),
  ('2020-01-12', '2020-02-12', '5', '6'),
  ('2020-01-31', '2020-02-22', '6', '6'),
  ('2020-01-05', '2020-02-14', '11', '8');

CREATE TABLE Product_order (
  `po_id` INTEGER,
  `product_id` INTEGER,
  `order_id` INTEGER
);

INSERT INTO Product_order
  (`po_id`, `product_id`, `order_id`)
VALUES
  ('1', '3', '3'),
  ('2', '13', '5'),
  ('3', '45', '6'),
  ('4', '7', '11');

CREATE TABLE products (
  `product_id` INTEGER,
  `product` VARCHAR(10),
  `price` DECIMAL(5,2)
);

INSERT INTO products
  (`product_id`, `product`, `price`)
VALUES
  ('3', 'Soda', '3.99'),
  ('13', 'Chips', '7.99'),
  ('45', 'Gum', '8'),
  ('7', 'GummyBears', '12');

select o.customer_id, sum(p.price) as total_sale
from (Select distinct order_id, customer_id
    from `Order`) as o
inner join Product_order po on o.order_id = po.order_id
inner join products p on po.product_id = p.product_id
group by o.customer_id

customer_id | total_sale
----------: | ---------:
          4 |       3.99
          6 |      15.99
          8 |      12.00

SELECT 
    c.name, p.product, pr.total_sale
FROM
    Customer c
        INNER JOIN
    `Order` o ON c.customer_id = o.customer_id
        INNER JOIN
    Product_order po ON o.order_id = po.order_id
        INNER JOIN
    products p ON po.product_id = p.product_id
        INNER JOIN
    (SELECT 
        o.customer_id, SUM(p.price) AS total_sale
    FROM
        (SELECT DISTINCT
        order_id, customer_id
    FROM
        `Order`) AS o
    INNER JOIN Product_order po ON o.order_id = po.order_id
    INNER JOIN products p ON po.product_id = p.product_id
    GROUP BY o.customer_id) pr ON c.customer_id = pr.customer_id

name | product    | total_sale
:--- | :--------- | ---------:
Adam | Soda       |       3.99
Drew | Chips      |      15.99
Drew | Gum        |      15.99
Tom  | GummyBears |      12.00

db<>fiddle here

Upvotes: 2

Related Questions