CODEWITHSUNDEEP
c#linq
vincentsty
vincentsty

Reputation: 3221

Linq select date of birth range and sum

I have a table like the following

UserID UserName DateOfBirth
1      User1    1988-1-1
2      User2    1978-1-1
3      User3    1959-1-1
....
.....

What I intend to do is to query a result similar to following

Age range   Total User
16 - 25     10
26 - 35     20
36 - 45     15
46 - 55     16
56 - 65     30
> 70        40

The problem i am having is i do not know how to group the age range as above. I only manage to group the age of each individual user using following code

db.User
.Where(p => p.DateOfBirth != null)
.GroupBy(p => p.DateOfBirth.Value.Year)
.Select(g => new { Age = DateTime.Now.Year - g.Key, Count = g.Count() });

Upvotes: 1

Views: 2078

Answers (2)

ataravati
ataravati

Reputation: 9155

Something like this should work: 

db.User.Where(p => p.DateOfBirth != null).ToList()
    .Select(p => new { UserId = p.UserId, Age = DateTime.Now.Year - p.DateOfBirth.Value.Year })
    .GroupBy(p => (int)((p.Age - 16) / 10))
    .Select(g => new { AgeGroup = string.Format("{0} - {1}", g.Key * 10 + 16, g.Key * 10 + 25), Count = g.Count() });

By the way, you can't just subtract the birth year from the current year to calculate the age. You'll have to create a function to calculate it. Here you can find out how to calculate the age.

UPDATE:

If you want to have a separate group for age range "> 65", then you can do it like this:

db.User.Where(p => p.DateOfBirth != null).ToList()
    .Select(p => new { UserId = p.UserId, Age = DateTime.Now.Year - p.DateOfBirth.Value.Year })
    .GroupBy(p => p.Age > 65 ? 5 : (int)((p.Age - 16) / 10))
    .Select(g => new { AgeGroup =  g.Key == 5 ? "> 65" : string.Format("{0} - {1}", g.Key * 10 + 16, g.Key * 10 + 25), Count = g.Count() });

You can do the same thing for ages below 16 too. If it gets more complicated, I recommend creating a separate function to get the Age Group string.

Upvotes: 1

JNYRanger
JNYRanger

Reputation: 7097

You first would need to create some sort of value that would represent being within a certain age-range if you want to do it using linq. This might work better as a Stored Procedure though if you're using a SQL database.

Currently, you are performing your grouping on the year as you know. You'd need a second operation to count up the matches within each range. Use the linq query that you currently have and then do the following (this assumes you have Dictionary<string,int> ageRanges within this class, but you can always modify this to have it elsewhere):

var query = /*LINQ QUERY HERE*/;
foreach(var g in query)
{
    int ranges = g.Age / 5; //range in example is every 10 years starting at 16
    switch(ranges)
    {
         case 0: //0 -5
         case 1: //5 - 9
         case 2: //10-15
             continue; //we don't care about these values
         case 3: //16-20
         case 4: //21-25
              addToRanges("16-25",g.Count);
              break;
         ... //etc you get the idea
     }
}

Here's our addToRanges method:

private void addToRanges(string key, int value)
{
    if(ageRanges.ContainsKey(key))
    {
        ageRanges[key] += value;
    }
    else
    {
        ageRanges.Add(key, value);
    }
}

There are lots of other ways to do this, but this is just some simple example on how you can perform this operation. This is not necessarily the most performant example. Having a Stored Procedure that performs this calculation would be the best/most performant way to do this, but this method will at least show you how to do it with the linq query that you already put together.

Here's a more linq like example:

db.User
  .Where(p => p.DateOfBirth != null)
  .GroupBy(p => p.DateOfBirth.Value.Year)
  .Select(g => 
   {
       int age = DateTime.Now.Year - g.Key;
       int range = (age / 5) - 3; //Assumes that everything is at least age > 15
       if(range % 2 == 1)
            range -= 1; //to make sure we group 10s not 5s
       return new { Age = age, Count = g.Count(), Range = range } 
   })
  .GroupBy(a => g.Range)
  .Select(g => new {RangeValue = g.Range, Count = g.Count()});

Now this will produce a similar result with an IEnumerable instead of a dictionary.

Upvotes: 0

Related Questions