Databases
StackOverflow2013
Postsdbo
POLeft outer join and group by
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLeft outer join and group by
    text
    copied!<p>I have the following class definition:</p> <pre><code>public class MyData { public DateTime Expiration { get; private set; } public string Name { get; private set; } public double Price { get; private set; } public double Quantity { get; private set; } } </code></pre> <p>I need to join this with a list of years:</p> <pre><code>IEnumerable&lt;int&gt; years= Enumerable.Range(1, 20); </code></pre> <p>The result will be ultimately be displayed in a grid with the y-axis representing the <code>Name</code> field and the x-axis representing the years. Each cell will be the aggregated <code>Quantity</code> * <code>Price</code> for the <code>Name</code> and year.</p> <p>I am currently struggling with the syntax. I started off by joining the instances of <code>MyData</code> to the years and grouping as follows:</p> <pre><code>var myData = GetData(); var query = from data in myData join year in years on (data.Expiration.Year - DateTime.Now.Year) + 1 equals year group data by new { Year = (data.Expiration.Year - DateTime.Now.Year) + 1 , Name = data.Name } into grouped select new {Name = grouped.Key.Name , Year = grouped.Key.Year , Value = grouped.Sum(d =&gt; d.Quanity * d.Price) }; </code></pre> <p>This gives me the data aggregated as I need, but obviously excludes any years where none of the instances of <code>MyData</code> contain a matching <code>Expiration</code>.</p> <p>I can't seem to figure out how to modify my query to get the data I need. Once I get the years included my aggregation breaks down, and I effectively end up with the sum of all the <code>Price</code>'s * <code>Quantity</code>'s for a <code>Name</code> across all years, rather than on a year by year basis.</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