0

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.

testuser181920
  • 3
  • 1

2 Answers2

1

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;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • That worked thank you! Is there a way to double "group by"? As now i need a new column D, that gives me the percentage share of the individual values in column C in the total of the values in column C within the group for which the values in column A are the same. The result desired would be: Column D: 74,1% 3.2% 22.5% 100% – testuser181920 Feb 13 '23 at 10:55
  • That worked. Thank you so much. next I tried to sum up the values in my tabel for each PARTITION BY A using GROUPING SETS. MY QUERY looks like this: 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 GROUPING SETS ((A, B), (A)); It works as intened, inserting a row that sums up my partition. But somehow all my values are cut in half? What am i doing wrong? – testuser181920 Feb 13 '23 at 14:30
  • Please consider _accepting_ this answer by clicking the green checkmark to the left. For more follow ups, maybe ask a new question. – Tim Biegeleisen Feb 13 '23 at 14:32
  • Accepted it. Thank you! Do you know why my SUM(SUM(C)) OVER (PARTITION BY A) AS PCT ist twice as big after my grouping sets statement? – testuser181920 Feb 13 '23 at 14:55
  • I actually have not worked much with `GROUPING SETS`...but maybe you don't even need to be using it. – Tim Biegeleisen Feb 13 '23 at 15:03
-2

You can do nested grouping. The first column in the order gets to group the result first and the following columns get grouped within the group of the previous group.

SELECT count(*) FROM table GROUP BY A, B;
prabhuraaj
  • 31
  • 1
  • 4