Generating Daily and Monthly sales Report

Question

Good day guys,

I have a table in SQLSERVER 2016 named TblOrder_Details, I want to generate daily report to show Qty, Price, Amount and also have a summary of the Total_amount sales in the day, and this should show other days within the range of days selected

The code below is what I have tried but I keep getting issues when I use the SUM() aggregate function as it does allow only grouping with one column

SELECT item_Name,qty, price, amount, Sum(amount) as Total_Amount 
FROM     tblOrderDetails  
where  Order_Date between @startdate and @enddate  GROUP BY Order_Date 

I am getting this report into a Crystal Report, But it does not display the result. I have tried using GROUP BY all Columns I selected, not rendering the way I want.

OUTPUT should be in form of

OrderDate: 2019/07/29
item_Name   qty   price    amount
rice         2     2,000    4,000
beans        4     4,000    16,000


Summary
Total_qty=2
Total_AMount = 20,000

Order_Date:  2019/07/30
item_Name   qty   price    amount
soap         2     2,500    5,000
slippers     4     3,000    12,000


Summary
Total_qty=2
Total_Amount = 17,000

Also if possible if I can generate for monthly sales and year sales too especially the report rendering in crystal

Please help guys

Answer 1

To get the desired output you can use this query (for daily report):

SELECT  item_Name ,
        qty ,
        price ,
        amount ,
        Total_Amount,
        Order_Date 
FROM    tblOrderDetails T
        INNER JOIN ( SELECT SUM(amount) AS Total_Amount ,
                            Order_Date
                     FROM   tblOrderDetails
                     WHERE  Order_Date BETWEEN @startdate AND @enddate
                     GROUP BY Order_Date
                   ) D ON D.Order_Date = T.Order_Date
WHERE   Order_Date BETWEEN @startdate AND @enddate;