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.

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)";

CodePudding user response：

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;