sum column to show total in every row

Question

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 1

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

Answer 2

Use a sub-query:

SELECT [Group], Sum(columnA) as SumColumn, 
       TotalColumn = (SELECT SUM(columnA) FROM dbo.Table1)
FROM dbo.Table1
GROUP BY [Group]

Demo

Answer 3

You can actually mix window functions and aggregation functions in a select statement. So, you can do this without subqueries:

select Group, Sum(columnA) as SumColumn,
       sum(sum(columnA)) over () as TotalColumns
FROM table
GROUP BY Group;

Answer 4

Though this may not be the most effective way to do it. Here is my answer.

SELECT a.*,
    (select Sum(columnA)
     FROM table) as [TotalColumn]
FROM (select Group, Sum(columnA) as SumColumn
      FROM table
      GROUP BY Group) as a