| |

VerySource

 Forgot password?
 Register
Search
View: 738|Reply: 7

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

[Copy link]

3

Threads

10

Posts

11.00

Credits

Newbie

Rank: 1

Credits
11.00

 China

Post time: 2020-2-14 12:30:02
| Show all posts |Read mode
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 ????
Reply

Use magic Report

0

Threads

126

Posts

73.00

Credits

Newbie

Rank: 1

Credits
73.00

 China

Post time: 2020-4-12 22:15:01
| Show all posts
sums 5 20% 50% 50% 0

How does this count?
Reply

Use magic Report

0

Threads

88

Posts

55.00

Credits

Newbie

Rank: 1

Credits
55.00

 China

Post time: 2020-4-13 11:15:01
| Show all posts
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
Reply

Use magic Report

0

Threads

126

Posts

73.00

Credits

Newbie

Rank: 1

Credits
73.00

 China

Post time: 2020-4-13 13:15:01
| Show all posts
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
Reply

Use magic Report

1

Threads

8

Posts

9.00

Credits

Newbie

Rank: 1

Credits
9.00

 China

Post time: 2020-4-16 00:30:01
| Show all posts
A little bit later in the program.
Reply

Use magic Report

0

Threads

126

Posts

73.00

Credits

Newbie

Rank: 1

Credits
73.00

 China

Post time: 2020-4-16 11:15:01
| Show all posts
If not, add a UNION
Reply

Use magic Report

0

Threads

211

Posts

108.00

Credits

Newbie

Rank: 1

Credits
108.00

 China

Post time: 2020-4-17 11:30:01
| Show all posts
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)
Reply

Use magic Report

3

Threads

10

Posts

11.00

Credits

Newbie

Rank: 1

Credits
11.00

 China

 Author| Post time: 2020-4-29 12:15:01
| Show all posts
Thank you, the problem has been solved
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2023|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list