locked
Linq - grouping by 2 columns RRS feed

  • Question

  • Hi

    I have this LINQ query that works ok but I now need to add an extra column into the mix. this query-

    Dim q = From p In sPreScoreTable
            Group p By Material = p("Material") _
            Into _
            Total = Sum(Convert.ToDouble(p("Total"))), _
            PercWeight = Sum(Convert.ToDouble(p("PercWeight"))), _
            EcoPoint = Sum(Convert.ToDouble(p("EcoPoint"))), _
            EC = Sum(Convert.ToDouble(p("EC"))), _
            RC = Sum(Convert.ToDouble(p("RC"))), _
            DisWeight = Sum(Convert.ToDouble(p("DisWeight"))), _
            EOL = Sum(Convert.ToDouble(p("EOL")))
            Select New With {Material, Total, PercWeight, EcoPoint, EC, RC, DisWeight, EOL}

    This will group all of the rows by Material and then sum all of the other fields to produce a result like this-

    I now need to add another column that isn't a numerical value. This is seen in the image (currently blank). All of the values, when entered into the table just before it is grouped together are identical and is the string "None".

    What I cannot get working is the grouping of this new column so that when the linq query groups, it is grouped by material, all of the numeric fields are summed and this new column to display "None" at the end. Example:

    Material     TW  % U  EP   EC    RC  DA     RA    ACCD
    MDF          10  30%  .5   .2    10  100     15   None
    MDF          25  50%  .3   .5    10  100     15   None
    Glass        50  30%  .1   1.4   50  100     20   None

    Linq Result

    Material     TW  % U  EP   EC    RC  DA     RA    ACCD
    MDF          35  80%  .8   .7    10  100     15   None
    Glass        50  30%  .1   1.4   50  100     20   None

    All columns added together except ACCD and grouped by Material

    How can I achieve this?

    Cheers

    Nigel


    Nacho is the derivative of Nigel "True fact!"

    Friday, October 30, 2015 6:05 AM

Answers

  • Hi

    Thanks for the reply. I managed to work a solution-

    Dim q = From p In sPreScoreTable
            Group p By Material = p("Material"), Accd = p("Accreditation") _
            Into _
            Total = Sum(Convert.ToDouble(p("Total"))), _
            PercWeight = Sum(Convert.ToDouble(p("PercWeight"))), _
            EcoPoint = Sum(Convert.ToDouble(p("EcoPoint"))), _
            EC = Sum(Convert.ToDouble(p("EC"))), _
            RC = Sum(Convert.ToDouble(p("RC"))), _
            DisWeight = Sum(Convert.ToDouble(p("DisWeight"))), _
            EOL = Sum(Convert.ToDouble(p("EOL")))
            Select New With {Material, Total, PercWeight, EcoPoint, EC, RC, DisWeight, EOL, Accd}

    Thanks

    Nacho


    Nacho is the derivative of Nigel "True fact!"

    Wednesday, November 4, 2015 3:53 AM

All replies

  • Hi Denis Ahearn,

    According to your description, I guess you want to group by two

    columns with Material and ACCD.

    So, we can do it like this:

    Var query =  From p In sPreScoreTable Group p By 

                        new {p. Material ,p. ACCD }  Into …

    Best Regards,

    Grady



    • Edited by Grady_Dong Tuesday, November 3, 2015 7:06 AM
    Friday, October 30, 2015 9:19 AM
  • Hi Nigel,

    You've not mentioned more info about ACCD column in table. What is the meaning of ACCD column?

    I guess you would like to statistics the Material column and just want to add a "None" column in the query result. If so, you just need add a new property "ACCD" in select statement in LIQN. If not, you could group the table by Material and ACCD column suggested by Grady.

    If I have any misunderstanding, please feel free to let me know.

    I hope it helps.

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, November 4, 2015 3:23 AM
  • Hi

    Thanks for the reply. I managed to work a solution-

    Dim q = From p In sPreScoreTable
            Group p By Material = p("Material"), Accd = p("Accreditation") _
            Into _
            Total = Sum(Convert.ToDouble(p("Total"))), _
            PercWeight = Sum(Convert.ToDouble(p("PercWeight"))), _
            EcoPoint = Sum(Convert.ToDouble(p("EcoPoint"))), _
            EC = Sum(Convert.ToDouble(p("EC"))), _
            RC = Sum(Convert.ToDouble(p("RC"))), _
            DisWeight = Sum(Convert.ToDouble(p("DisWeight"))), _
            EOL = Sum(Convert.ToDouble(p("EOL")))
            Select New With {Material, Total, PercWeight, EcoPoint, EC, RC, DisWeight, EOL, Accd}

    Thanks

    Nacho


    Nacho is the derivative of Nigel "True fact!"

    Wednesday, November 4, 2015 3:53 AM