CODEWITHSUNDEEP
sqlsql-servert-sqlsum
hoggar
hoggar

Reputation: 3817

Sum rows grouped by value in first column - T-SQL

I need query that will do the sum of the colB grouped by colA and then multiply with value from colA. After all summing for different groups all sums should be added in total sum. Number of different values in colA could be any.

colA    colB
0.25    100
0.25     80
0.10    200
0.50    160
0.10    500
0.10    150

For given example math should be as follows:

(100+80)*0.25 + (200+500+150)*0.10 + 160*0.50 = 210

Upvotes: 0

Views: 892

Answers (1)

db_brad
db_brad

Reputation: 923

If order did matter in this case, you would do something LIKE this:

SELECT SUM(Total) AS GrandTotal
FROM (
SELECT  t.ColA, t.ColA * SUM(t.ColB) AS Total
FROM    dbo.Table1 AS t
GROUP BY t.ColA
    ) AS A

But this should work in this case:

SELECT SUM(ColA * ColB)
FROM dbo.Table1

Upvotes: 2

Related Questions