请建议我如何获得列“总计”行中的值，currenlty具有以下查询 [英] please suggest How do I get column "Total" value in row , currenlty have below query

问题描述

下面的查询给出了指定，COunt，总计的输出，这里总计是计数总和，但总值30应该是最后一行的结尾，请建议，我使用的是cte expr，





with cte

as

（

select cm。 cm_value为'Designation'，count（im.ticket_no）为'Count'

from im_ticketmaster im

inner join user_master um on im.caller = um.userid

内部联接common_master cm on um.Designation = cm.cm_id

group by cm.cm_value）

，cte2 as

（

选择总和（计数）总计来自cte

）

select * from cte，cte2





以下实际产量





指定总数

......................................

客户经理4 30

客户经理2 30

会计师2 30

会计16 30

AP / PR服务5 30

助理总监1 30

< br $>


所需产量



指定数量

....... ...............................

客户经理4

客户经理2

会计师2

会计16

AP / PR服务5

助理总监1
TOTAL 30

解决方案

尝试使用union

  SELECT 名称，计数
  FROM  cte 
  UNION  
  SELECT  ' 总计'  as 名称，总计 as 计数
  FROM  cte2; 

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

解决方案

try with union

SELECT  Designation, Count
FROM    cte
UNION
SELECT  'Total' as Designation, Total as Count
FROM    cte2;

这篇关于请建议我如何获得列“总计”行中的值，currenlty具有以下查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！