group by date from date column in yyyy-mm-dd format-CodePudding

I have a table with these columns: order_id, order_status, order_date, order_quantity, sales, discount_value, product_category, product_sub_category

order date is in yyyy - dd - mm format. i want to query so that the result would be in table like this: (https://i.ibb.co/cFh4h4K/Capture2.png)

i tried the formula below but it instead gives me so many rows based on the year like this (https://i.ibb.co/fDcjpks/Capture2.png).

SELECT 
LEFT(order_date, 4) as years, 
SUM(order_quantity*sales)-SUM(order_quantity*discount_value) AS sales, 
SUM(order_quantity) AS number_of_order 
FROM dqlab_sales_store
WHERE order_status = 'order finished' 
GROUP BY order_date,order_quantity,discount_value;

any help? thanks

CodePudding user response：

If you want to group by the year, how about try to use this?

group by years

This is because you save yyyy as a years so the column name will be a years not a order_date. I hope my answer will solve your problem.

CodePudding user response：

is this what you're looking for?

SELECT 
LEFT(order_date, 4) as years, 
SUM(order_quantity*sales)-SUM(order_quantity*discount_value) AS sales, 
SUM(order_quantity) AS number_of_order 
FROM dqlab_sales_store
WHERE order_status = 'order finished' 
GROUP BY LEFT(order_date, 4);