wondergoat77 wondergoat77 - 1 month ago 7
SQL Question

sum column to show total in every row

I have a query that looks like this:

select Group, Sum(columnA) as SumColumn
FROM table
GROUP BY Group


I get results looking like this

+-----+---------+
|Group|SumColumn|
+-----+---------+
|A |10 |
+-----+---------+
|B |20 |
+-----+---------+


How can I change/add to this to show something like this?

+-----+---------+-----------+
|Group|SumColumn|TotalColumn|
+-----+---------+-----------+
|A |10 |30 |
+-----+---------+-----------+
|B |20 |30 |
+-----+---------+-----------+

Answer

It is hard to see what your data looks like -- but from what you posted this is what you want:

SELECT Name, 
       SumColumn,
       SUM(SumColumn) AS TotalColumn
FROM
(
    SELECT Group as Name, SUM(columnA) AS SumColumn
    FROM Table 
    GROUP BY Group
) T

You might want this -- depending on other stuff.

SELECT *,  
       SUM(columnA) OVER (PARTITION BY Group ORDER BY Group) AS SumColumn,
       SUM(columnA) OVER (PARTITION BY Group) AS TotalColumn
FROM TABLE
Comments