please suggest How do I get column "Total" value in row , currenlty have below query

See more:

SQL

SQL-Server

query below gives me output of Designation , COunt , total , here total is sum of count , but total value 30 should be a at the end last row , please suggest, I am using the cte expr ,


with cte
as
(
select cm.cm_value as 'Designation', count(im.ticket_no) as 'Count'
from im_ticketmaster im
inner join user_master um on im.caller = um.userid
inner join common_master cm on um.Designation=cm.cm_id
group by cm.cm_value )
, cte2 as
(
select sum(count) Total from cte
)
select * from cte,cte2


Actual Output below


Designation Count Total
......................................
Account Executive 4 30
Account Manager 2 30
Accountant 2 30
Accounting 16 30
AP/PR Services 5 30
Assistant Director 1 30


Required output

Designation Count
......................................
Account Executive 4
Account Manager 2
Accountant 2
Accounting 16
AP/PR Services 5
Assistant Director 1
TOTAL 30