Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have Orders table which has daily orders. I would like to create a summary table which aggregates the data by month. Storing daily transactions is making my database huge and takes long time to process. I don't need to store the values by day.
Tried the below code, but its not working.
OrdersSummaryTbl:
Load Transaction_Date,
MonthStart(Transaction_Date) as MonthStart,
Country,
Product_Name,
Sum(Order_Count) as Orders,
Sum(Order_Amount) as OrderAmt
Resident Orders
Group by Transaction_Date,
Country,
Product_Name
MonthStart;
I just need to aggregate the data by month and store it. Please can you share your thoughts.
try this
OrdersSummaryTbl:
Load
//Transaction_Date,
MonthStart(Transaction_Date) as MonthStart,
Country,
Product_Name,
Sum(Order_Count) as Orders,
Sum(Order_Amount) as OrderAmt
Resident
Orders
Group by
MonthStart(Transaction_Date),
Country,
Product_Name
//MonthStart
;
OrdersSummaryTbl:
Load
Country,
Product_Name,
Month
Sum(Order_Count) as Orders,
Sum(Order_Amount) as OrderAmt
Group by
Country,
Product_Name,
Month;
Load
Transaction_Date,
Month(Transaction_Date) as Month,
Country,
Product_Name,
Order_Count
Order_Amount
Resident
Orders;
try this
OrdersSummaryTbl:
Load
//Transaction_Date,
MonthStart(Transaction_Date) as MonthStart,
Country,
Product_Name,
Sum(Order_Count) as Orders,
Sum(Order_Amount) as OrderAmt
Resident
Orders
Group by
MonthStart(Transaction_Date),
Country,
Product_Name
//MonthStart
;
Hi,
Try this:
//-------------------
OrdersSummaryTemp:
Load
Year(Transaction_Date) & '-' & Month(Transaction_Date) as KeyDate,
Country,
Product_Name,
Order_Count,
Order_Amount
Resident Orders;
OrdersSummaryTbl:
KeyDate
Country,
Product_Name,
Sum(Order_Count) as Orders,
Sum(Order_Amount) as OrderAmt,
Resident OrdersSummaryTemp
Group by KeyDate, Country, Product_Name;
DROP TABLE OrdersSummaryTemp;
//--------------------------
Regards,
Piotr
Thank you all. It worked.