select * from (
select 名称, 数量, 单价, 金额
from tbname
union all
select top 1 '合计',null,null,sum(金额) from tbname
)aa
select 名称, 数量, 单价, 金额
from tbname
union all
select top 1 '合计',null,null,sum(金额) from tbname
)aa
union all select '合计' as 名称,' ' as 数量,sum(金额) as 金额 from 表
declare @t table(名称 varchar(10),数量 int,单价 money,金额 money)
insert into @t select 'aaa',10,1.21,12.10
union all select 'bbb',11,1.22,13.40
union all select 'ccc',12,1.00,12.00
--查询
select * from @t
union all
select '合计',NULL,NULL,sum(金额) from @t
--结果
名称 数量 单价 金额
---------- ----------- --------------------- ---------------------
aaa 10 1.2100 12.1000
bbb 11 1.2200 13.4000
ccc 12 1.0000 12.0000
合计 NULL NULL 37.5000(所影响的行数为 4 行)
insert into @t select 'aaa',10,1.21,12.10
union all select 'bbb',11,1.22,13.40
union all select 'ccc',12,1.00,12.00
--查询
select isnull([名称],'合计') as '名称'
,sum([数量]) as '数量'
,case when grouping([名称])=1 then '' else convert(varchar,avg([单价])) end as '单价'
,sum([金额]) as '金额'
from @t
group by [名称]
with rollup--结果
/*名称 数量 单价 金额
---------- ----------- ------------------------------ ---------------------
aaa 10 1.21 12.1000
bbb 11 1.22 13.4000
ccc 12 1.00 12.0000
合计 33 37.5000(所影响的行数为 4 行)
*/
declare @t table(名称 varchar(10),数量 int,单价 money,金额 money)
insert into @t select 'aaa',10,1.21,12.10
union all select 'bbb',11,1.22,13.40
union all select 'ccc',12,1.00,12.00
--查询
select *
from @t
compute sum(金额)
FROM TABLE1
UNION ALL
SELECT '合计', '', '', SUM(金额)
FROM table1
create table Price
(
名称 varchar(10),
数量 int,
单价 money,
金额 money
)
insert into Price select 'aaa',10,1.21,12.10
insert into Price select 'bbb',11,1.21,12.10
insert into Price select 'ccc',12,1.21,12.10---查询
select *
from Price
where 单价 is not null
compute sum(金额)