I am trying to find the total sales made for distinct Items within specific period.

For instance, I have several items sold in a day, I want the cumulative total of each itme sold for that day and not just picking it one after the other as it was saved to the database.

Below is what I have presently.

Instead for it to be listing each item separately, I want it to pick an item and then add the quantities for the item together as below

The code I have tried is this:

"SELECT Order_Date,ItemName,qty,price 
 FROM tblOrderDetails 
 WHERE [Order_date] BETWEEN @startdate AND @enddate
ORDER BY order_date"

Is there a better way this can be done both using SQL and LINQ

I have also tried the below SQL Command but it keeps bringing error that the Order_Date must be part of the Group BY

SELECT  DISTINCT(Product_Name), SUM(qty) AS Qty, Order_date 
FROM tblOrderDetails WHERE [Order_date] BETWEEN @startdate AND @enddate group by product_id, Order_date 
order by Order_date

CodePudding user response：

This should work

SELECT product_id, Product_Name, Price, SUM(qty * Price) as amount, SUM(qty) AS Qty, Order_date 
FROM tblOrderDetails 
WHERE Order_date BETWEEN @startdate AND @enddate 
group by product_id, Product_Name, Order_date, price 
order by Order_date

CodePudding user response：

Try the following (not tested)

SELECT  ItemName, price, SUM(qty) AS Qty
FROM tblOrderDetails 
WHERE [Order_date] BETWEEN @startdate AND @enddate 
GROUP BY ItemName, price 

Not sure where order_date plays a part other than in selection, but to match your script ...

SELECT  [Order_date], ItemName, price, SUM(qty) AS Qty
FROM tblOrderDetails 
WHERE [Order_date] BETWEEN @startdate AND @enddate 
GROUP BY [Order_date], ItemName, price