# GroupBy on 1 Column - calculate the Average and Sums of the group members

I need to write this query in the Assign activity. Can you help me?
There are city, house and price columns in Excel. I will print how many houses there are in each city and the average price of the houses in each city.

My file like this, model is instead of house

@Busra1

may we ask you to replace the sample data with a focus on houses and their samples?
Please also check the requirement if the Date col is to incorporate into the calculations.
Thanks for support

Yes, we can think of it as a model instead of houses. No the date is not included in the calculations

lets assume following sample data:

preparing the target datatable dtTarget with a build datatable

we can do:

``````dtTarget =
(From d In dtData.AsEnumerable
Group d By k=d("Location").toString.Trim Into grp=Group
Let s = grp.Sum(Function (s) CInt( s(0).toString.Trim))
Let a = grp.Average(Function (a) CInt(a(1).toString.Trim))
Let ra = New Object(){k,s,a}
``````

AVG for A = `(100+250)/2`

or more precise on the Avg

``````(From d In dtData.AsEnumerable
Group d By k=d("Location").toString.Trim Into grp=Group
Let tp = grp.Sum(Function (t) CInt(t(0).toString.Trim) * CInt(t(1).toString.Trim ))
Let s = grp.Sum(Function (s) CInt( s(0).toString.Trim))
Let a =tp/s
Let ra = New Object(){k,s,a}
``````

Avg for A = `(2*100+3*250) / (2+3)`

Also have a look here for LINQ and GroupBy

Hi @Busra1 ,

you can use this LINQ query also:

``````(
From row In Excel_DT
Let xCityName = row("City").ToString.Trim
Group row By a = xCityName Into grp = Group
Let xCount = CStr(grp.Count)
Let xSum = grp.Sum(Function (s) CInt( s("Price").ToString.Trim))
Let xAverage = grp.Average(Function (av) CInt(av("Price").toString.Trim))