CODEWITHSUNDEEP
c#sql-serverlinqentity-framework-5
SeToY
SeToY

Reputation: 5895

Querying SQL Server for percentages

I've got three tables: Customers, Origins and (the mapping table) CustomerOrigins. Please note that a customer can originate from more than one Origin (for example Internet, Advertisement, Newsletter)

Now I want to show a pie chart of where my customers originate from.

A result should be a List<string, double>.

I'm totally lost, even in how to start this whole query... Any hints would be greatly appreciated.

Upvotes: 2

Views: 129

Answers (1)

p.s.w.g
p.s.w.g

Reputation: 149020

You can't make a List<string, double> because List<T> only takes one type parameter. Try this instead:

var results =
    (from c in db.Customers
     from o in c.Origins
     group o by o.DisplayName into g
     let total = db.Customers.Count()
     select new
     {
         Origin = g.Key,
         Percent = ((double)g.Count() / (double)total) * 100
     })
    .ToList();

This has the possibility of counting a single customer multiple times (once fore each origin), so you won't come up with a list that totals 100%.

If you want a percentage over CustomerOrigin records, try this

var results =
    (from c in db.Customers
     from o in c.Origins
     group o by o.DisplayName into g
     let total = db.Customers.Sum(x => x.Origins.Count())
     select new
     {
         Origin = g.Key,
         Percent = ((double)g.Count() / (double)total) * 100
     })
    .ToList();

Upvotes: 2

Related Questions