create table T (ID int, product ID char (4), time char (5), v1 int, v2 int, v3 int, v4 int, sums int)
insert T select 1, '1001', '17: 10 ', 0, 0, 0, 0, 0
union all select 2, '1002', '17: 20 ', 1, 0, 1, 0, 2
union all select 3, '1003', '17: 30 ', 0, 1, 0, 0, 1
union all select 4, '1004', '17: 40 ', 0, 0, 1, 0, 1
union all select 5, '1005', '17: 50 ', 0, 1, 0, 0, 1
select ID = rtrim (ID), product ID, time, rtrim (v1), rtrim (v2), rtrim (v3), rtrim (v4), sums from T
union all
select 'sums', count (*), '', rtrim (sum (v1)), rtrim (sum (v2)), rtrim (sum (v3)), rtrim (sum (v4)), sum (sums) from T
union all
select 'sums', count (*), '', rtrim (sum (v1) * 100 / sum (sums)) + '%', rtrim (sum (v2) * 100 / sum (sums)) + '%' , rtrim (sum (v3) * 100 / sum (sums)) + '%', rtrim (sum (v4) * 100 / sum (sums)) + '%', NULL from T
--result
ID product ID time sums
------------ ----------- ----- ------------- --------- ---- ------------- ------------- -----------
1 1001 17:10 0 0 0 0 0 0
2 1002 17:20 1 0 1 0 2
3 1003 17:30 0 1 0 0 1
4 1004 17:40 0 0 1 0 1
5 1005 17:50 0 1 0 0 1
sums 5 1 2 2 0 5
sums 5 20% 40% 40% 0% NULL
(7 row (s) affected) |