Search code examples
sqlgroup-byheidisql

SQL: GROUP BY column B for groups in column a

I am looking for a SQL query that will sum the values in column C GROUP BY column B, but not over the entire table, only within the ranges where the same values are present in column A.

Current table:

Column A Column B Column C
A B 10
A B 5
A B 8
A C 1
A D 7
B B 10
B B 5

Required table:

Column A Column B Column C
A B 23
A C 1
A D 7
B B 15

SELECT A, B, SUM(C) FROM ... WHERE ... GROUP BY B

This obviously doesn't work, because I don't use column A with an aggregate function, nor in the GROUP BY statement. How do I get to the desired solution? Thanks for the help.

Solution

  • It seems that you want to group by the first two columns and sum the third:

    SELECT A, B, SUM(C) AS SUM_C,
       100.0 * SUM(C) / SUM(SUM(C)) OVER (PARTITION BY A) AS PCT
FROM yourTable
GROUP BY A, B;