Question

sql语句 最后一行增加合计

select shuliang as 数量 ,shoujia as 售价 from dingdan select '合计金额 ',sum(shuliang * shoujia)
from dingdan

怎么才能把合计 加到最后一行啊

Answer 1

select shuliang as 数量 ,shoujia as 售价 from dingdan
UNION ALL
select '合计金额 ' as 数量 ,sum(shuliang * shoujia) as 售价
from dingdan

Answer 2

select shuliang as 数量 ,shoujia as 售价 from dingdan
union
select '合计金额 ' as 数量,sum(shuliang * shoujia) as 售价
from dingdan

追问

消息 245，级别 16，状态 1，第 1 行
在将 varchar 值 '合计金额 ' 转换成数据类型 int 时失败。

追答

select cast(shuliang as varchar(2) ) as 数量 ,shoujia as 售价 from dingdan
union
select '合计金额 ' as 数量,sum(shuliang * shoujia) as 售价
from dingdan

追问

怎么让合计到最后一行

select shangpinming as 商品名,shuliang as 数量 ,shoujia as 金额 from dingdan 
union
select '','合计金额',sum(shuliang*shoujia) from dingdan

Answer 3

drop table temp;
create table temp(
num varchar2(10),
price number(10,2)
);
insert into temp values(3,11145);
insert into temp values(4,245);
insert into temp values(3,83);
insert into temp values(1,49);
insert into temp values(30,145);
commit;
select t.num,t.PRICE from temp t 
union 
select '合计金额',sum(x.price) from temp x

输出结果

1    1    49
2    3    83
3    3    11145
4    30    145
5    4    245
6    合计金额    11667
用UNION时，t.num(数量)和 ‘合计金额’ 的数据类型必须相同

追问

给了个空值 合计就跑上面去了

追答

select t.num,t.PRICE from temp t
union
select null,sum(x.price) from temp x

不对啊，我的是在下面的