CODEWITHSUNDEEP
c#linq
Pavan
Pavan

Reputation: 337

Get Average of column using group by

I am trying to get the average of property from table based on its group,

from avgtbl1 in table1
group avgtbl1 by new
{
    avgtbl1 .Prop1,
    avgtbl1 .Prop2,
    avgtbl1 .Prop3,
    avgtbl1 .Prop4
} into tempavgtbl1 
select new
{
    Prop1= tempavgtbl1 .Key.Prop1,
    Prop2= tempavgtbl1 .Key.Prop2,
    Prop3= tempavgtbl1 .Key.Prop3,
    Prop4= tempavgtbl1 .Key.Prop4,
    prop5= tempavgtbl1 .Average(a =>  a.Prop4)
};

After executing the query it's gives same value of prop4 rather than a average of group for prop4 which is based on prop1,prop2,prop3. I am not getting where i went wrong.

 |Prop1 | prop2 |prop3| prop4|
 |1     | abc   |123  |    20|
 |1     | abc   |123  |    24|
 |2     | abc   |123  |    20|
 |2     | abc   |123  |    24|
 |3     | pqr   |  123|    27|
 |3     | pqr   |  123|    29|



 Expected Result

 |Prop1 | prop2 |prop3| prop4|prop5|
 |1     | abc   |123  |    20| 22  |
 |1     | abc   |123  |    24| 22  |
 |2     | abc   |123  |    21| 22.5|
 |2     | abc   |123  |    24| 22.5|
 |3     | pqr   |  123|    27| 28  |
 |3     | pqr   |  123|    29| 28  |

Current result:

 |Prop1 | prop2 |prop3| prop4|prop5|
 |1     | abc   |123  |    20|   20|
 |1     | abc   |123  |    24|   24|
 |2     | abc   |123  |    21|   21|
 |2     | abc   |123  |    24|   24|
 |3     | pqr   |  123|    27|   27|
 |3     | pqr   |  123|    29|   29|

Upvotes: 1

Views: 89

Answers (1)

juharr
juharr

Reputation: 32266

The problem is that you are grouping on Prop4. To do what you want you'll have to calculate the average then join back to the original table to get the un-averaged values. Also you can just group Prop4 by the other columns so that you don't have to specify the column in the Average.

from a1 in table1
group a1.Prop4 by new
{
    a1.Prop1,
    a1.Prop2,
    a1.Prop3
} into grp
join a2 in table1 
on grp.Key equals new {a2.Prop1, a2.Prop2, a2.Prop3};    
select new
{
    a2.Prop1, // or grp.Key.Prop1,
    a2.Prop2, // or grp.Key.Prop2,
    a2.Prop3, // or grp.Key.Prop3,
    a2.Prop4,
    Prop5 = grp.Average()
}

Alternatively this can also be done with a subquery.

from a1 in table1 
select new
{
    a1.Prop1, 
    a1.Prop2, 
    a1.Prop3, 
    a1.Prop4,
    Prop5 = (from a2 in table1
             where a2.Prop1 = a1.Prop1
                   && a2.Prop2 = a1.Prop2
                   && a2.Prop3 = a1.Prop3
             select a2.Prop4).Average()
}

Upvotes: 1

Related Questions