Answered by:
Linq  grouping by 2 columns
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 &amp;amp;quot;True fact!&amp;amp;quot;
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 &amp;amp;quot;True fact!&amp;amp;quot;
 Proposed as answer by Herro wongMicrosoft contingent staff Friday, November 6, 2015 8:00 AM
 Marked as answer by Herro wongMicrosoft contingent staff Monday, November 9, 2015 6:28 AM
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 &amp;amp;quot;True fact!&amp;amp;quot;
 Proposed as answer by Herro wongMicrosoft contingent staff Friday, November 6, 2015 8:00 AM
 Marked as answer by Herro wongMicrosoft contingent staff Monday, November 9, 2015 6:28 AM
Wednesday, November 4, 2015 3:53 AM