CODEWITHSUNDEEP
sql-serverpivot
Rakesh
Rakesh

Reputation: 594

Create a grand total row in sql server

I'm writing a query where in there should be a total row at the end of the table that gives the sum of the closed , open, reassigned columns.

I'm able to make a pivot, but unable to know how to place a row in the end. I've searched online but mostly found solutions for creating a column and a row or just column. I'm into Dev, dunno much about the databases. below is my pivot. please let me know how can i do this.

select *
from 
(
  select [case owner] as AGENT, [final status]
  from StatusTable
) src
pivot
(
  count([final status])
  for [final status] in ([CLOSED], [OPEN], [REASSIGNED])
) piv

The current output is as below image

enter image description here

And here I want an extra row as shown below.

TOTAL | 2 | 8 | 2

Upvotes: 1

Views: 6114

Answers (3)

gordy
gordy

Reputation: 9786

If you write your query as a group by instead of a pivot, you can use with rollup to get your total row. E.g.:

select
  [AGENT] = case when grouping([case owner])=1 then 'TOTAL' else [case owner] end,
  [CLOSED] = sum(case when [final status] = 'CLOSED' then 1 else 0 end),
  [OPEN] = sum(case when [final status] = 'OPEN' then 1 else 0 end),
  [REASSIGNED] = sum(case when [final status] = 'REASSIGNED' then 1 else 0 end)
from StatusTable
group by [case owner]
with rollup

http://sqlfiddle.com/#!3/e0bb5/8/0

Upvotes: 1

Bacon Bits
Bacon Bits

Reputation: 32155

Assuming agent is a key field, you can aggregate on that and use GROUP BY ROLLUP():

select coalesce(piv.agent,'Grand Total') agent
    ,sum(piv.closed) closed
    ,sum(piv.open) open
    ,sum(piv.reassigned) reassigned
from 
(
  select [case owner] as AGENT, [final status]
  from StatusTable
) src
pivot
(
  count([final status])
  for [final status] in ([CLOSED], [OPEN], [REASSIGNED])
) piv
group by rollup(piv.agent)

Upvotes: 1

Lucero
Lucero

Reputation: 60190

You can use UNION ALL to concat rowsets with identical columns:

with ctePivot as (
    select *
    from 
    (
      select [case owner] as AGENT, [final status]
      from StatusTable
    ) src
    pivot
    (
      count([final status])
      for [final status] in ([CLOSED], [OPEN], [REASSIGNED])
    ) piv
)
select * from ctePivot
union all
select 'TOTAL', sum([CLOSED]), sum([OPEN]), sum([REASSIGNED]) from ctePivot

Or the simpler variant with only a GROUP BY ROLLUP:

select ISNULL([case owner], 'TOTAL') as AGENT, 
    SUM(CASE WHEN [final status]='CLOSED' THEN 1 ELSE 0 END) [CLOSED],
    SUM(CASE WHEN [final status]='OPEN' THEN 1 ELSE 0 END) [OPEN],
    SUM(CASE WHEN [final status]='REASSIGNED' THEN 1 ELSE 0 END) [REASSIGNED]
    from StatusTable
    GROUP BY ROLLUP ([case owner]);

Upvotes: 5

Related Questions