sql - 在子查询中添加总计

Question

我正在尝试在底部添加一个总计，但似乎不明白如何获得在底部显示“总计”的语法。我已经用尽了时间在网上搜索它。我需要合并代码段，因为我需要为没有销售额的员工显示零。我查看了此链接，但我需要合并中的零。

Add a row for TOTAL in a sql query result

select t2.Employee, coalesce(t1."This Week",0) "This Week"
from mytable t2 left outer join
(select case when grouping(Employee) = 1 then 'Total' else Employee end, sum(Sales) "This Week"
from information
where Week >= DATE '01/01/2017' and Week < DATE '01/31/2017'
and LastUpdate >= DATE '01/01/2017' and LastUpdate < DATE '01/31/2017'
group by Employee with Rollup) t1
on t1.Employee = t2.Employee

结果:

Employee                    This Week 

 Batman                       15
 Penguin                      25
 Joker                        0
 Bane                         5
 Scarecrow                    0
 ------------------->         45

错误:

ERROR:  syntax error at or near "with"
LINE 8: group by Employee with Rollup) t1

Answer 1

你可以用 ROLLUP 试试这个

SELECT coalesce(Employee,'Total'),
       "This Week"
FROM
  (SELECT t2.Employee,
          coalesce(sum(t1.Sales),0) "This Week"
   FROM mytable t2
   LEFT JOIN information t1 ON t1.Employee = t2.Employee
   AND t1.Week >= DATE '01/01/2017'
   AND t1.Week < DATE '01/31/2017'
   AND t1.LastUpdate >= DATE '01/01/2017'
   AND t1.LastUpdate < DATE '01/31/2017'
   GROUP BY rollup(t2.Employee)
  ) x