Get sales total and GROUP BY DAY with Sale price x Amount -Discount

Question

I'm trying get a total of sales by day teaking into count the amount of item sold and if any discount apply. This is what I have.

item_sale_price	item_sale_qty	discount_percentage	sale_date
250000	5	10	2021-06-01
100000	1	0	2021-06-02
25000	2	5	2021-06-02

SELECT item_sale_price * item_sale_qty - (item_sale_price * item_sale_qty - (discount_percentage / 100)) AS total_per_day 
FROM sales_items where sale_date BETWEEN '$startdate' AND '$enddate' GROUP BY DAY(sale_date)";

and where is the problem? Also your code is **vulnerable** to **sql injection** so swithc to **prepared statements with parameters** see https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — nbk, Oct 24 '21 at 11:18

score 1 · Accepted Answer · answered Oct 24 '21 at 11:20

You must multiply item_sale_price by 1 - discount_percentage / 100 to get the price after discount.
Also you should use SUM() aggregate function to get the total:

SELECT SUM(item_sale_qty * item_sale_price * (1 - discount_percentage / 100)) AS total_per_day 
FROM sales_items 
WHERE sale_date BETWEEN '$startdate' AND '$enddate' 
GROUP BY DAY(sale_date);

I'm not sure why you group by DAY(sale_date) and not just sale_date.

This makes more sense:

SELECT sale_date,
       SUM(item_sale_qty * item_sale_price * (1 - discount_percentage / 100)) AS total_per_day 
FROM sales_items 
WHERE sale_date BETWEEN '$startdate' AND '$enddate' 
GROUP BY sale_date;

You are right @forpas there is no need of having DAY(). I thank you for your help :-). — Andres Carvajal, Oct 24 '21 at 11:35

Get sales total and GROUP BY DAY with Sale price x Amount -Discount

1 Answers1