CODEWITHSUNDEEP
asp.net.netasp.net-mvcentity-frameworkentity-framework-4
JamesM94
JamesM94

Reputation: 61

Group count by month

I am trying to return results for how many customers have 'enrolled' within a month. At the moment it returns a count per specific dates. see below:

Count for customers enrolled per day

Here is the code in my controller:

 public ActionResult About()
        {
            IQueryable<EnrollmentDateGroup> data = from customer in db.Customers
                                                   group customer by customer.EnrollmentDate  into dateGroup

                                                   select new EnrollmentDateGroup()
                                                   {
                                                       EnrollmentDate = dateGroup.Key,
                                                       CustomerCount = dateGroup.Count() 

        };

Can anyone advise on how i can have a count for how many customers enrolled per month?

Thanks in advance.

UPDATE

I get the following error when i use:

group customer by new { customer.EnrollmentDate.Year, customer.EnrollmentDate.Month } into dateGroup

enter image description here

Upvotes: 0

Views: 840

Answers (3)

ISHIDA
ISHIDA

Reputation: 4868

Try this. 

var result = from s in data
             group s by new { date = new DateTime(s.EnrollmentDate.Year, s.EnrollmentDate.Month, 1)} into g 
select new {
date = g.Key.date,
Sum = g.Sum(x=>x.CustomerCount)
};

Upvotes: 0

Chris Pratt
Chris Pratt

Reputation: 239290

You need to group by the month and year, alone. When you pass the full date, it includes a day component that's going to mess up the grouping. For example:

group customer by new { Year = customer.EnrollmentDate.Year, Month = customer.EnrollmentDate.Month }

UPDATE

That's because you're tying to set the key directly to your EnrollmentDate propery, and the key is now an anonymous type, not a DateTime. Either create a DateTime or do something different.

EnrollmentDate = new DateTime(m.Key.Year, m.Key.Month, 1),

Upvotes: 1

Sailor
Sailor

Reputation: 183

Use casting in your GROUPBY clause

GROUP BY CAST(MONTH(date) AS VARCHAR(2)) + '-' + CAST(YEAR(date) AS VARCHAR(4))

Upvotes: 0

Related Questions