Question

SQL明细及汇总，请帮我在最后一行增加行合计

select b.name,c.quantity as 数量,c.saleprice as 底价单价,c.total as 底价金额,c.discount as 折扣,c.discountprice as 高价单价,
c.totalmoney as 高价金额,c.costprice as 成本价,c.SendCostTotal as 成本金额
from billidx a,clients b,salemanagebill c
where b.name like '%威宁县盐仓退休医师医院%'
and a.billdate>='2014-07-25'
and a.c_id=b.client_id
and a.billid=c.bill_id
and a.billstates ='0'
and A.billtype='10'
union all
select b.name,-c.quantity as 数量,-c.saleprice as 底价单价,-c.total as 底价金额,-c.discount as 折扣,-c.discountprice as 高价单价,
-c.totalmoney as 高价金额,-c.costprice as 成本价,-c.SendCostTotal as 成本金额
from billidx a,clients b,salemanagebill c
where b.name like '%威宁县盐仓退休医师医院%'
and a.billdate>='2014-07-1'
and a.c_id=b.client_id
and a.billid=c.bill_id
and a.billstates ='0'
and A.billtype='11'

查询结果如下：
name 数量 单价1 金额2 折扣 单价2 金额2 单价3 金额3
威宁县盐仓退休医师医院 100.0000 1.6 160.0000 1.375 2.2 220.0000 1.4600 146.0000

威宁县盐仓退休医师医院 200.0000 2.8 560.0000 1.3571 3.8 760.0000 2.4000 480.0000
威宁县盐仓退休医师医院 200.0000 2.8 560.0000 1.3571 3.8 760.0000 2.4000 480.0000
威宁县盐仓退休医师医院 300.0000 2.4 720.0000 1.1667 2.8 840.0000 2.0250 607.5000
威宁县盐仓退休医师医院 100.0000 1.8 180.0000 1.3889 2.5 250.0000 1.2128 121.2800
威宁县盐仓退休医师医院 200.0000 2.7 540.0000 2.1481 5.8 1160.0000 1.8180 363.6000
威宁县盐仓退休医师医院 -30.0000 -1.3 -39.0000 -1.1538 -1.5 -45.0000 -0.9000 -27.0000
威宁县盐仓退休医师医院 -500.0000 -2.6 -1300.0000 -1.9769 -5.14 -2570.0000 -2.0200 -1010.0000

Answer 1

with tb1 as (你的查询语句)

select * from tb1
union
select name+'合计',sum(数量),avg(单价1),sum(金额2),avg(折扣),avg(单价2),sum(金额2),avg(单价3),sum(金额3) from tb1 group by name

个人觉得单价和折扣应该按平均来算，如果要累加自己改一下函数

追问

有提示出错【
消息 102，级别 15，状态 1，第 2 行
'tb1' 附近有语法错误。
】

-

追答

with tb1 as (你的查询语句)

查询语句要用()括起来