Agregate rows with two or more columns with same value

Question

In asp.net c#, I have a large set of values. I'm trying to agregate rows with same values and sum some of their values (The objective is to have a row in a table with the agreggated info)... is there a way of agreggating rows with two or more columns with same values and doing a sum of a third column?

Table Example:

id      | column1 | column2  |column to sum
--------+---------+----------+-------------
1       | 3       | 1        |10
2       | 3       | 1        |10
3       | 3       | 2        |10
4       | 4       | 2        |10
5       | 4       | 2        |10
6       | 7       | 3        |10
7       | 7       | 3        |10
8       | 7       | 3        |10
9       | 5       | 1        |10
10      | 5       | 4        |10

Objective: Sum values of "column to sum" of rows that have the same column1 and column2 values

It would end like this:

id      | column1 | column2  |column to sum
--------+---------+----------+-------------
-       | 3       | 1        |20
3       | 3       | 2        |10
-       | 4       | 2        |20
-       | 7       | 3        |30
9       | 5       | 1        |10
10      | 5       | 4        |10

Is there a way of doing this with EF or LINQ or SQL STATEMENTS?

Thank you

Answer 1

In SQL,

SELECT column2, colum1, SUM(column to sum) GROUP BY column2, colum1

In LINQ Query syntax (untested, but should be something like below)

           var result = from res in myTableOrClassList
                     group res by new { res.Column1, res.Column2 } into g
                     select new { column1 = g.Key.column1, colum2 = g.Key.column2, columnSum = g.Sum(f => f.columToSum) };

Linq Method syntax

    var linq = myTableOrClassList.GroupBy(p=> new { p.Column1, p.Column2 }).
                    Select(k=> new {column1 = k.Key.Column1, column2 = k.Key.Column2, sumColumn = k.Sum(each => each.columToSum) });

Answer 2

Simple GROUP BY should do your job. If you select the id you are going to get redundant columnToSum.

WITH CTE AS (
SELECT 1 as id, 3 as column1, 1 as column2,10 as [column to sum]
UNION
SELECT 2 as id, 3 as column1, 1 as column2,10 as [column to sum]
UNION
SELECT 3 as id, 3 as column1, 2 as column2,10 as [column to sum]
UNION
SELECT 4 as id, 4 as column1, 2 as column2,10 as [column to sum]
UNION
SELECT 5 as id, 4 as column1, 2 as column2,10 as [column to sum]
UNION
SELECT 6 as id, 7 as column1, 3 as column2,10 as [column to sum]
UNION
SELECT 7 as id, 7 as column1, 3 as column2,10 as [column to sum]
UNION
SELECT 8 as id, 7 as column1, 3 as column2,10 as [column to sum]
UNION
SELECT 9 as id, 5 as column1, 1 as column2,10 as [column to sum]
UNION
SELECT 10 as id, 5 as column1, 4 as column2,10 as [column to sum]
)

SELECT column1,column2,SUM([column to sum]) [column to sum] FROM CTE
GROUP BY column1,column2