none
Linq noob - group and sum RRS feed

  • Question

  • I'm trying to get into the swing of Linq, can someone point me in the right direction with this query sample?  Running against PUBS:

    Code Snippet

    from a in Authors
    join ta in Titleauthors on a.Au_id equals ta.Au_id
    join t in Titles on ta.Title_id equals t.Title_id
    select new { a.Au_lname, ta.Royaltyper }


    This gives me a nice set of results of last names and royaltiesper.  I'd like to group the names to get a sum of the royaltiesper value.  I know it doesn't make sense from a real world point of view, but it shows what I'm trying to achieve.

    I get this:

    ...
    Green       40
    Green       100
    ...
    Ringer       50
    Ringer       100
    Ringer       25
    Ringer       50
    ...
    Straight     100
    ...

    What I'd like is this:

    ...
    Green     140
    ...
    Ringer     225
    ...
    Straight    100
    ...

    Can anyone help?  In SQL I'd just do a group by last name and sum on the value.  How can I do the same in Linq?
    Thursday, April 10, 2008 8:06 PM

Answers

  • Hi,

     

    I received a notification that you posted an answer but I didn't find that entry in this thread.

     

    That's the SQL you have

    SELECT     authors.au_lname AS FamilyName, SUM(titles.royalty) AS Royalties
    FROM         titles INNER JOIN
                          titleauthor ON titles.title_id = titleauthor.title_id RIGHT OUTER JOIN
                          authors ON titleauthor.au_id = authors.au_id
    GROUP BY authors.au_lname

    and you want to transform it to Linq.

    This SQL is an outer join which means you want to get all authors regardless if they have titles.

    This can NOT be done by

    from a in DC.authors

    from ta in a.titleauthors

    as I mentioned before, because this is an inner join (you only get the authors which have titles).

    The outer join or group join in Linq is

    var qry2 = from a in DC.authors

    join ta in DC.titleauthors

    on a.au_id equals ta.au_id into AuthorTitles

    Important here is the into clause (without it you only have an inner join). This is also named group join in Linq since it gives you a group of titles for every author (This group maybe empty).

    From this outer join you have to select the information you need and the whole query is:

    Code Snippet

    var qry2 = from a in DC.authors

    join ta in DC.titleauthors

    on a.au_id equals ta.au_id into AuthorTitles

    select new

    {

    FamiliyName = a.au_lname,

    Royalities = AuthorTitles.Sum(t => t.title.royalty),

    };

     

    foreach (var item in qry2)

    {

    Console.WriteLine("{0} : {1}", item.FamiliyName, item.Royalities);

    }

     

     

    If you don't need the authors without an title it's done by an inner join and this is

    Code Snippet

    var qry2 = from a in DC.authors

    from ta in a.titleauthors

    group ta.title.royalty by a.au_lname into grp

    select new { FamilyName = grp.Key, Royalities = grp.Sum() };

     

    foreach (var item in qry2)

    {

    Console.WriteLine("{0} : {1}", item.FamilyName, item.Royalities);

    }

     

     

     

    regards

    Philipp

    Sunday, April 13, 2008 6:51 PM

All replies

  • Hi,

     

    if you have direct links between tables you don't need to join them.

    Code Snippet

    static void Main(string[] args)

    {

    PubsDataContext DC = new PubsDataContext();

    var qry1 = from a in DC.authors

    from ta in a.titleauthors

    group new { a.au_lname, ta.royaltyper } by a.au_lname into grp

    select new { Author = grp.Key, RT = grp.Sum(x => x.royaltyper) };

    foreach (var item in qry1)

    {

    Console.WriteLine("{0} : {1}", item.Author, item.RT);

    }

    Console.ReadKey();

    }

     

     

    var qry1 = from a in DC.authors

    from ta in a.titleauthors   <---- this is same as joining the two tables, it's follow up the relation

     

    Next is grouping on a.au_lname and the elements in the group should be new { a.au_lname, ta.royaltyper }

     

    Then select the gouping key (in this case group ... by a.au_lname into ...) and build the sum of all Element in the group. To be precise sum over the property royaltyper of each element in the group.

     

    regards

    Philipp

    Thursday, April 10, 2008 10:00 PM
  • Hi,

     

    the query in my last post isn't wrong but could be done more easier. I build an anonymous object in the group clause but this isn't necessary since I have the au_lname in the group by.

    That's the shorter version

    Code Snippet

    var qry2 = from a in DC.authors

    from ta in a.titleauthors

    group ta.royaltyper by a.au_lname into grp

    select new { Author = grp.Key, RT = grp.Sum() };

     

     

    regards

    Philipp

    Thursday, April 10, 2008 10:13 PM
  • Thanks Philipp - how would I also add author name to the anonymous object?  I imagine it needs to be added to the initial 'from' since it's not in the titleauthors table?
    Friday, April 11, 2008 11:30 AM
  • from a in DC.authors

    from ta in a.titleauthors

    gives you each author and for each author the related titleauthors (exactly the same as your join)

     

    group ta.royaltyper by a.au_lname into grp

    or general group ElementSelector by KeySelector into GroupName.

    The GroupName has a standard property Key which is the key selected/defined by KeySelector. In the above case I used the property au_lname from author to group the join result.

    What each group should contain is defined by the ElementSelector. In the above case it's the property royaltyper from titleauthors which belongs to the authors whose au_lname is equal to the selected group key.

    GroupName (in the above case it's grp) is just a name you can use to refer the group.

    If you want to have more than one field in the group you need to define

    group new { a.au_lname, ta.royaltyper } by a.au_lname into grp

    Here I say each group should contains a.au_lname (a refers to authors) and ta.royaltyper (ta refers to a.titleauthors). Now a.au_lname doesn't make sense since it is the grouping key (it's always the same in one group).

     

    After joining the table and grouping the result set you want to select

    select new { Author = grp.Key, RT = grp.Sum() };

    This defines an anonymous object with two properties Author and RT (for royaltyper). Author is the groupkey ( = a.au_lname) and RT is the sum over all the elements in the group. Since the group only contains integers (=ta.royaltyper) it's the sum over all ta.royaltyper which belongs to the key a.au_lname.

    If your group contains object and not simples types you have to tell Linq what it should use for building the sum.

    select new { Author = grp.Key, RT = grp.Sum(x => x.royaltyper) };

    Here I'm telling Linq that for every object x it should use x.royaltyper to build the sum.

    But you can only aggregate (Sum,Max,Min,...) what's inside the group. You can't pick up a property from the join which isn't part of the group.

     

    regards

    Philipp

    Friday, April 11, 2008 1:18 PM
  • Hi,

     

    I received a notification that you posted an answer but I didn't find that entry in this thread.

     

    That's the SQL you have

    SELECT     authors.au_lname AS FamilyName, SUM(titles.royalty) AS Royalties
    FROM         titles INNER JOIN
                          titleauthor ON titles.title_id = titleauthor.title_id RIGHT OUTER JOIN
                          authors ON titleauthor.au_id = authors.au_id
    GROUP BY authors.au_lname

    and you want to transform it to Linq.

    This SQL is an outer join which means you want to get all authors regardless if they have titles.

    This can NOT be done by

    from a in DC.authors

    from ta in a.titleauthors

    as I mentioned before, because this is an inner join (you only get the authors which have titles).

    The outer join or group join in Linq is

    var qry2 = from a in DC.authors

    join ta in DC.titleauthors

    on a.au_id equals ta.au_id into AuthorTitles

    Important here is the into clause (without it you only have an inner join). This is also named group join in Linq since it gives you a group of titles for every author (This group maybe empty).

    From this outer join you have to select the information you need and the whole query is:

    Code Snippet

    var qry2 = from a in DC.authors

    join ta in DC.titleauthors

    on a.au_id equals ta.au_id into AuthorTitles

    select new

    {

    FamiliyName = a.au_lname,

    Royalities = AuthorTitles.Sum(t => t.title.royalty),

    };

     

    foreach (var item in qry2)

    {

    Console.WriteLine("{0} : {1}", item.FamiliyName, item.Royalities);

    }

     

     

    If you don't need the authors without an title it's done by an inner join and this is

    Code Snippet

    var qry2 = from a in DC.authors

    from ta in a.titleauthors

    group ta.title.royalty by a.au_lname into grp

    select new { FamilyName = grp.Key, Royalities = grp.Sum() };

     

    foreach (var item in qry2)

    {

    Console.WriteLine("{0} : {1}", item.FamilyName, item.Royalities);

    }

     

     

     

    regards

    Philipp

    Sunday, April 13, 2008 6:51 PM
  • how can I use Class PubsDataContext,I don know where the NameSpace

    Wednesday, August 19, 2015 3:34 AM