basic question about totals in sql server

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

COHENMARVIN@lycos.com - 29 Jul 2008 21:31 GMT

Suppose I have a table of numbers. I want to display that table, but
I also want to display the sum of the number in each column as the
last row of the table. Can I do that? I know I can do a 'group by'
with aggregate functions, but in this case I wouldn't want to group,
I'd just want a result set whose last row is totals. One idea I have
is to do a 'union' of two queries, the second query being the 'sum'
query. Is there an easier way?
(This would be useful in asp.net pages.)
Thanks,
Marv

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 29 Jul 2008 21:44 GMT

Do you mean

SELECT a, b, c, total = (a + b + c)
FROM table?

If so, there is no magic way to do it... you can drag the columns node from
Object Explorer onto a query window to shorten your work a bit...

A

> Suppose I have a table of numbers. I want to display that table, but
> I also want to display the sum of the number in each column as the
[quoted text clipped - 6 lines]
> Thanks,
> Marv

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 29 Jul 2008 22:08 GMT

I mixed up rows and columns. Yes, after re-reading I think the easiest way
would be a UNION ALL.

On 7/29/08 4:44 PM, in article #hOL8ub8IHA.3480@TK2MSFTNGP03.phx.gbl, "Aaron
Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote:

> Do you mean
>
[quoted text clipped - 16 lines]
>> Thanks,
>> Marv

Reply to this Message

Plamen Ratchev - 29 Jul 2008 22:14 GMT

Depending on your query, you may be able to use GROUP BY <columns> WITH
ROLLUP. That will add subtotal and total rows for the grouping columns.

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message