CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
oshirowanen
oshirowanen

Reputation: 15925

SQL Server Counting

I have the following query:

select col1, sum( col2 ), count( col3 )
from table1
group by col1
order by col1

which returns something like this

col1
dept1
dept2
dept3

col2
10
20
30

col3
2
3
4

Without a stored procedure, is it possible to get a total column below the results generated by the original query?

i.e.

col1
dept1
dept2
dept3
total

col2
10
20
30
60

col3
2
3
4
9

Upvotes: 2

Views: 119

Answers (3)

Ruslan
Ruslan

Reputation: 10147

yep:

select col1, sum(col2), count(col3)
from table1
group by col1
union all
select 'totals', sum(col2), count(1) from table1
order by col1

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

use ROLLUP:

;with Table1 as (
    select 'dept1' as col1, 5 as col2,1 as col3
    union all
    select 'dept1', 5 as col2, 1 as col3
    union all
    select 'dept2',10,1
    union all
    select 'dept2',5,1
    union all
    select 'dept2',5,1
    union all
    select 'dept3',10,1
    union all
    select 'dept3',5,1
    union all
    select 'dept3',5,1
    union all
    select 'dept3',10,1
)
select COALESCE(col1,'total'), sum( col2 ), count( col3 )
from table1
group by col1
with rollup
order by COALESCE(col1,'ZZZZZ')

Results:

(No column name)    (No column name)    (No column name)
dept1               10                  2
dept2               20                  3
dept3               30                  4
total               60                  9

Upvotes: 5

SPE109
SPE109

Reputation: 2951

Have a look at the keyword WITH ROLLUP on your GROUP BY clause

Upvotes: 1

Related Questions