Group product by date and sum up quantity and total sales

Question

Hello Team, 

Please I need your assistance, in my controller I want to group sales by date and sum of the quantity and total sales

public ActionResult GetAllSalesRecord()
        {
            ASPNETMASTERPOSTEntities db = new ASPNETMASTERPOSTEntities();

            var dataList = db.tblSales.Join(
                db.tblSalesDetails,
                sales => sales.SalesId,
                detail => detail.SalesId,
                (sales, detail) => new { Sales = sales, detail = detail });
            var modifiedData = dataList.Select(x => new

            {
                SalesDetailId = x.detail.SalesDetailId,
                Quantity = x.detail.Quantity,
                ProductId = x.detail.ProductId,
                ProductName = x.detail.tblProduct.ProductName,
                SalesId = x.Sales.SalesId,
                OrderDate = x.Sales.OrderDate,
                TotalAmount = x.Sales.TotalAmount
            }).ToList();
            return Json(modifiedData, JsonRequestBehavior.AllowGet);
        }

Answer 1

Hello Emma,

Please try GroupBy. check the below code to get expected result.

public ActionResult GetAllSalesRecord()
{
    ASPNETMASTERPOSTEntities db = new ASPNETMASTERPOSTEntities();
    var dataList = db.tblSales.Join(
       db.tblSalesDetails,
       sales => sales.SalesId,
       detail => detail.SalesId,
       (sales, detail) => new { Sales = sales, detail = detail });

    var modifiedData = dataList.GroupBy(x => new {x.Sales.OrderDate, x.detail.ProductId}).Select(x => new
    {
      SalesDetailId = x.FirstorDefault().Sales.SalesDetailId,
      Quantity = x.Sum(a => a.detail.Quantity),
      ProductId = x.key.ProductId,
      ProductName = x.FirstorDefault().detail.tblProduct.ProductName,
      SalesId = x.FirstorDefault().Sales.SalesId,
      OrderDate = x.key.OrderDate,
      TotalAmount = x.Sum(a => a.Sales.TotalAmount)
    }).ToList();
    return Json(modifiedData, JsonRequestBehavior.AllowGet);
}

Thanks

Answer 2

public ActionResult GetAllSalesRecord()
{
    ASPNETMASTERPOSTEntities db = new ASPNETMASTERPOSTEntities();

    var dataList = db.tblSales.Join(
        db.tblSalesDetails,
        sales => sales.SalesId,
        detail => detail.SalesId,
        (sales, detail) => new { Sales = sales, Detail = detail });

    var modifiedData = dataList.Select(x => new
    {
        SalesDetailId = x.Detail.SalesDetailId,
        Quantity = x.Detail.Quantity,
        ProductId = x.Detail.ProductId,
        ProductName = x.Detail.tblProduct.ProductName,
        SalesId = x.Sales.SalesId,
        OrderDate = x.Sales.OrderDate,
        TotalAmount = x.Sales.TotalAmount
    }).ToList();

    var groupedData = modifiedData
        .GroupBy(x => x.OrderDate.Date) // Group by date (ignoring time)
        .Select(g => new
        {
            OrderDate = g.Key,
            TotalQuantity = g.Sum(x => x.Quantity),
            TotalSales = g.Sum(x => x.TotalAmount)
        }).ToList();

    return Json(groupedData, JsonRequestBehavior.AllowGet);
}

Answer 3

Thank you team for your response, am so much greatful

Answer 4

public ActionResult GetAllSalesRecord()
{
    ASPNETMASTERPOSTEntities db = new ASPNETMASTERPOSTEntities();

    var dataList = db.tblSales
        .Join(
            db.tblSalesDetails,
            sales => sales.SalesId,
            detail => detail.SalesId,
            (sales, detail) => new { Sales = sales, Detail = detail })
        .GroupBy(x => x.Sales.OrderDate) // Grouping by OrderDate
        .Select(group => new
        {
            OrderDate = group.Key,
            TotalQuantity = group.Sum(x => x.Detail.Quantity),
            TotalSales = group.Sum(x => x.Sales.TotalAmount)
        }).ToList();

    return Json(dataList, JsonRequestBehavior.AllowGet);
}