Databases
StackOverflow2013
Postsdbo
POMultiple Group data
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMultiple Group data
    text
    copied!<p>I have a set of data as below from my <code>Payment</code> table</p> <pre><code>DateIssue | Amount | CoursePaidForMonth | 2/3/2011 9:54:07 PM | 2000.00| 2 2/27/2011 2:22:58 PM | 80.00| 2 3/5/2011 11:14:56 PM | 80.00| 3 3/27/2011 2:22:58 PM | 80.00| 2 2/8/2011 6:32:45 PM | 80.00| 2 </code></pre> <p>I would like to perform two sets of grouping for the data above:</p> <ol> <li><p>The closing date is on the 27 of every month, so I would like to group all the data from 27 till 26 of next month into a group. This is done successfully from the <code>gName</code>. NO PROBLEM !! Check out the output as the image below.</p> <pre><code>DateIssue AppendCommToMonthΞΞ CoursePaidForMonthΞΞ TotalAmountΞΞ 2/8/2011 6:32:45 PM 2 2 2080.00 2/27/2011 2:22:58 PM 3 2 160.00 3/27/2011 2:22:58 PM 4 2 80.00 </code></pre></li> <li><p>Now I would like to perform another grouping on the same query. I would like to group the <code>CoursePaidForMonth</code> as well. Meaning to say, the final output should show 4 lines of records instead of just 3, it group by from 27 this month till 26 of next month into a group and also by <code>CoursePaidForMonth</code>. How to do this?</p> <pre><code>DateIssue AppendCommToMonthΞΞ CoursePaidForMonthΞΞ TotalAmountΞΞ 2/8/2011 6:32:45 PM 2 2 2080.00 2/27/2011 2:22:58 PM 3 2 80.00 3/5/2011 11:14:56 PM 3 3 80.00 3/27/2011 2:22:58 PM 4 2 80.00 </code></pre></li> </ol> <p>My code is here:</p> <pre><code>var result = from p in db.Payments join soi in db.SaleOrderItems on p.ReferenceId equals soi.Id join ct in db.CourseByTutors on soi.InventoryOrCourseId equals ct.CourseId where p.PayType == (int)PayTypes.PayCourseFee &amp;&amp; ct.TutorId == tutorId let gName = (p.DateIssue.Value.Day &lt; 27) ? (p.DateIssue.Value.Month) : (p.DateIssue.Value.Month % 12 + 1) group p by new { gName} into g select new { DateIssue = g.Select(x =&gt; x.DateIssue).First(), AppendCommForWhichMonth = g.Key.gName, CoursePaidForMonth = g.Select(x =&gt; x.CoursePaidForMonth).First(), TotalAmount = g.Sum(x =&gt; x.Amount), }; </code></pre> <p>Please advice..</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload