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
image

@Busra1
Thanks for your PM

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

Thanks for replying
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:
grafik

preparing the target datatable dtTarget with a build datatable
grafik

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}
Select r=dtResult.Rows.Add(ra)).CopyToDataTable

grafik
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}
Select r=dtResult.Rows.Add(ra)).CopyToDataTable

grafik
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))
Select out_DT.Rows.Add({a,xCount,xSum,xAverage})
      ).CopyToDataTable

Test_LINQ_Group_Sum_Count.xaml (7.4 KB)
Test.xlsx (9.7 KB)