how to show total cost across all orders per customer?

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

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top