Newbie seeking a SQL question (online, etc ...)

命犯桃花

A table temp already exists:
ID Product ID time v1 v2 v3 v4 sums
1 1001 17: 10 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

Now I want to count the above data and get the following look
ID Product ID time v1 v2 v3 v4 sums
1 1001 17: 10 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% 50% 50% 0

How to solve it ????

wgfxman

sums 5 20% 50% 50% 0

How does this count?

cv4bf5d65fd

select cast (ID as varchar (10)) id, cast (product ID as varchar (10)) product ID, cast (time as varchar (10)) time, cast (v1 as varchar (10)) v1, cast (v2 as varchar (10)) v2, cast (v3 as varchar (10)) v3, cast (v4 as varchar (10)) v4, cast (sums as varchar (10)) sums from temp
union all
select 'sums' ID, count (1) product ID, '' time, str (sum (v1)) v1, str (sum (v2)) v2, str (sum (v3)) v3, str (sum (v4) ) v4, str (sum (sums)) sums from temp
union all
select 'sums' ID, count (1) product ID, '' time, str (sum (v1) / count (1) * 100) + '%' v1, str (sum (v2) / count (1) * 100 ) + '%' v2, str (sum (v3) / count (1) * 100) + '%' v3, str (sum (v4) / count (1) * 100) + '%' v4, '' sums from temp

wgfxman

select (CASE WHEN GROUPING (time) = 1 THEN 'sums' ELSE CAST (MAX (id) AS CHAR (5)) END),
(CASE WHEN GROUPING (time) = 1 THEN COUNT (1) ELSE MAX (product ID) END),
Time, sum (v1), sum (v2), sum (v3), sum (v4), sum (sums)
from T GROUP BY time with ROLlUP

lone_lau

A little bit later in the program.

wgfxman

If not, add a UNION

fanfan1980

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)

命犯桃花

Thank you, the problem has been solved