How to calculate Statistics for orders group by year in MYSQL?

user_16620074 0
Viewed 71

Order table.

id created_date price
1 2021-09-01 100
2 2021-09-01 50
3 2020-09-01 50
4 2020-09-01 150
5 2019-09-01 100

Order details table:-

id order_id product_quantity
1 1 10
2 2 20
3 1 30
4 3 10
5 3 20
6 4 30
7 5 20

So I need a MySQL query to get stats about order count and price and product quantity for each year.
The expected table

year(created_date) orders_count product_quantity_in_orders price
2021 2 60 150
2020 2 60 150
2019 1 20 100
querydsl spring mysql java
1 Answers

Please try this.

SELECT YEAR(o.created_date) year_created
     , COUNT(o.id) orders_count
     , SUM(t.product_quantity) product_quantity_in_orders
     , SUM(o.price) price 
FROM orders o
INNER JOIN (SELECT order_id
                 , SUM(product_quantity) product_quantity
            FROM order_details
            GROUP BY order_id) t
        ON o.id = t.order_id
GROUP BY YEAR(o.created_date)
user_163646250
Related