SQL Add row of Total from a current column

Question

Trying to add a summary row of Grand Totals from the existing Weekly_Total column - (this is coming from a temp table,not that that matters) -

SELECT
Rep, Monday, Tuesday, Wednesday, Thursday, Friday, Weekly_Total
FROM #bl_reptemp3

Which returns:

and I just want to add one row under Weekly_Total, with a sum of thhat column - can't seem to figure it out.

Related: https://stackoverflow.com/questions/35583753/summary-and-total-invoice-data-in-sql-query/35583968#35583968 — dfundako, Apr 16 '18 at 18:16
Possible duplicate of [How do I UPDATE from a SELECT in SQL Server?](https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server) — Nerdi.org, Apr 16 '18 at 18:18
Possible duplicate of [Add a summary row with totals](https://stackoverflow.com/questions/17934318/add-a-summary-row-with-totals) — Tab Alleman, Apr 16 '18 at 18:47

Nerdi.org · Answer 1 · 2018-04-16T18:17:01.907

1

SELECT SUM(columnName)

is how you get a total of a column

So...

INSERT INTO table (columns, go, here) SELECT 'value', 'val', SUM(here) FROM table

You just need a simple INSERT SELECT statement.

edited Apr 16 '18 at 18:17

answered Apr 16 '18 at 18:05

Nerdi.org

895
6
13

INSERT INTO table (column) SELECT SUM(column) FROM table – Nerdi.org Apr 16 '18 at 18:10
Everything I've told you is enough for you to do what you want to do. – Nerdi.org Apr 16 '18 at 18:11

score 1 · Accepted Answer · answered Apr 16 '18 at 18:15

1

try

SELECT
Rep, Monday, Tuesday, Wednesday, Thursday, Friday, Weekly_Total
FROM #bl_reptemp3

UNION

SELECT
Null Rep, Null Monday, NULL Tuesday, Null Wednesday, Null Thursday, NULL Friday, 
SUM(Weekly_Total) Weekly_Total
FROM #bl_reptemp3

answered Apr 16 '18 at 18:15

yoyoyoyo123

2,362
2
22
36

Guess u haven't understood required OP, Op wants a Weekly_total Column as output – Ven Apr 16 '18 at 18:17
@Ven OP wants a row. – dfundako Apr 16 '18 at 18:17
@bvmcode, thanks so much, that works, I never understood UNION. – M21 Apr 16 '18 at 18:21
What...? You asked how to insert a row that has a total of a column. And you're marking an answer that doesn't do that.. ? – Nerdi.org Apr 16 '18 at 18:22
@Nerdi.org he wants a blank row, and just a sum of the weekly column. – yoyoyoyo123 Apr 16 '18 at 18:23
Yeah... INSERT INTO table (weekly_total) SELECT SUM(weekly_total)... It's a one liner. – Nerdi.org Apr 16 '18 at 18:24
...You ASKED how to INSERT a row. The answer told you how to SELECT information. I don't see how this answer differs from any of the others. You should have clarified you didn't need to insert data into an actual table. You said temp table, so I took that as being you want to insert data into a table that you will eventually delete. It sounds more like you just needed a SELECT query to show the data within your DB gui. In the future please try to clarify these things if you can – Nerdi.org Apr 16 '18 at 19:39
"add a row to my result" is not the same thing as INSERT into the table, I didn't ask for INSERT, I asked how to add a total row, and the accepted answer did exactly that, perfectly. – M21 Apr 17 '18 at 19:24

score -1 · Answer 3 · answered Apr 16 '18 at 18:10

-1

You don't need to use Aggregate here, simply add columns

Select Rep, Monday,Tuesday,Wednesday,Thursday,Friday,
         Monday+Tuesday+Wednesday+Thursday+Friday As weekly_total
from Mytable

In case you have null values in column

use isnull(monday,0)

answered Apr 16 '18 at 18:10

Ven

2,011
1
13
27

Your addition would produce a weekly_total column which OP already has. – dfundako Apr 16 '18 at 18:17
Highlighting Op's requirement `I just want to add one row under Weekly_Total, with a sum of thhat column` – Ven Apr 16 '18 at 18:18
@m21 Can u please clarify what is your required output as sample data – Ven Apr 16 '18 at 18:19
Mhmm... INSERT SELECT... With sum(column). Y'all are overcomplicating it – Nerdi.org Apr 16 '18 at 18:20

score -2 · Answer 4 · answered Apr 16 '18 at 18:09

The way to add total rows in SQL Server is to use WITH ROLLUP. It doesn't look like your query is doing any aggregations, so you might need to add it to your query that is populating the temp table.

SELECT some_value, SUM(some_other_value) AS summed_value
FROM your_table
GROUP BY some_value
WITH ROLLUP

SQL Add row of Total from a current column

4 Answers4