CODEWITHSUNDEEP
mysql
Sachin
Sachin

Reputation: 1698

How to calculate total amount of products sold per year?

I have 3 tables:

  1. products (it has product id, price etc.)
  2. orders (it has order id, date etc.)
  3. orders_products (it has orders detail like product quantity etc.)

I want to calculate total amount of products sold per year. To achieve this, I wrote following query:

SELECT YEAR(`o`.`date_purchased`) AS year,
       SUM(`op`.`products_quantity`) AS TotalUnits,
       `p`.`products_id`,
       `p`.`products_price` AS price,
       ( SUM(`op`.`products_quantity`) * `p`.`products_price` ) AS TotalAmount
FROM   `orders` AS `o`
       INNER JOIN `orders_products` AS `op`
               ON `o`.`orders_id` = `op`.`orders_id`
       INNER JOIN `products` AS `p`
               ON `op`.`products_id` = `p`.`products_id`
GROUP  BY `p`.`products_id`
ORDER  BY year

It gives me following result set:

enter image description here

It gives me total amount grouped by product. But I want to get total amount grouped by year instead.

So for year 2016 (and for other years too), I want following result:

2016     10,606 (since 6478 + 632 + 2072 + 1424)

But I can't calculate total amount grouped by year because each product has a different price. So how can I modify my existing query to get total amount of products sold per year?

If it can't be achieved by MySQL alone, then I will not hesitate to use PHP.

Upvotes: 0

Views: 825

Answers (1)

Nick
Nick

Reputation: 147166

You just need to bring the multiplication of quantity and price into the SUM so that each product's quantity is multiplied by its own price:

SELECT YEAR(`o`.`date_purchased`) AS year,
       SUM(`op`.`products_quantity`) AS TotalUnits,
       SUM(`op`.`products_quantity` * `p`.`products_price`) AS TotalAmount
FROM   `orders` AS `o`
INNER JOIN `orders_products` AS `op` ON `o`.`orders_id` = `op`.`orders_id`
INNER JOIN `products` AS `p` ON `op`.`products_id` = `p`.`products_id`
GROUP  BY year
ORDER  BY year

Upvotes: 2

Related Questions