Linq query with multiple GroupBy

Question

Hey I have a linq query that I cannot make any sense of. It draws from a table to get sales data for a specific company(collection of stores).

The sales table is a collection of individual sale items, multiple sale items can be on one bill. I need to make a query that sorts a day worth of sales by store and then counts the total Bills for that store for that day and gives the average bill for that store for that day.

Here is the start of what I have so far

 //get list of all stores bill count/average bill FactSales.Where(d => d.DateKey >= 20130920).Where(d => d.DateKey <= 20130920) .Where(c => c.CompanyID == 4).GroupBy(g=>new{g.StoreID, g.BillID}).Select(g=>new{Store = g.Key.StoreID, Amount = g.Sum(a => a.Amount).Value })

This gets me a list of all individual bills but how do I get the average and bill count for each store in a list that would contain : storeID, Avg Bill(Sum of all store bills/billcount) and the Bill Count of each store?

Ive been at this awhile and read many posts and I must be missing something very obvious.

Any help will be greatly appreciated!

Vadim Martynov · Accepted Answer · 2016-10-30 19:54:13Z

FactSales // filter sale positions by day .Where(d => d.DateKey >= 20130920 && d.DateKey <= 20130920) // filter sale positions by company .Where(c => c.CompanyID == 4) // group values by store and bill so each row will contains all sales in one bill .GroupBy(g=>new { g.StoreID, g.BillID }) // calculate total amount for each bill .Select(g=>new { Store = g.Key.StoreID, Bill = g.Key.BillID, Amount = g.Sum(a => a.Amount).Value }) // group bills by store so each row will contains all bills with total amount for this store .GroupBy(g => g.Store) // calculate bills count and avg amount for this store .Select(g => new { Store = g.Key, Bills = g.Count(), AvgBill = g.Average(x => x.Amount) });

I thing that g.Sum(a => a.Amount).Value should not contains Value property and you can do just g.Sum(a => a.Amount).

James · Accepted Answer · 2016-10-31 06:04:16Z

i thk this will also work :

var result = FactSales .Where(d => d.DateKey >= 20130920 && d.DateKey <= 20130920) .Where(c => c.CompanyID == 4) .GroupBy(t => new { t.StoreId }) .Select(X => new { Store = X.Key.StoreId, Sum = X.Sum(t => t.Amount), Bills = X.Count(), average = X.Sum(t => t.Amount) / X.Count() });

isn't.

Collectives™ on Stack Overflow

Linq query with multiple GroupBy

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related