How to calculate the percentage of statistics?-CodePudding

How to calculate the percentage of statistics?

CodePudding user response:

"STATUS", "SHULIANG
"41 "" V", "
"C", "699"
"D", "19"
"J", "172"

For example, I want to calculate the percentage of the quantity that a list of all quantity, 41/sum (shuliang), how to write SQL, old error, ask Daniel

CodePudding user response:

No, you stick figures out Chou Chou

CodePudding user response:

refer to the second floor baidu_36457652 response:

is not right, you put the data posted Chou Chou

Upstairs is data

CodePudding user response:

 
The select ratio_to_report (SHULIANG) over (zb), t. * from t

CodePudding user response:

I want to ask how do you write standard SQL?

CodePudding user response:

41/sum (shuliang) over ()?

CodePudding user response:

Should be the sum () over () can do

CodePudding user response:

You can use the sum () over (), can also use a subquery to calculate the total,

CodePudding user response:

refer to 6th floor nayi_224 response:

41/sum (shuliang) over ()?

refer to 7th floor baidu_36457652 response:

should sum () over () can do

refer to the eighth floor wmxcn2000 response:

can use the sum () over (), you can also use a subquery to calculate the total,

Is there a over standard SQL function? Can use standard SQL to solve this problem, the subquery?

CodePudding user response:

Select the STATUS, SHULIANG, 41/sum (SHULIANG) over () from table

CodePudding user response:

Select t. *, t.S HULIANG/(select sum (SHULIANG) from t) from t.
Select t. *, t.S HULIANG/t2. Su from t, (select sum (SHULIANG) su from t) t2;

Standard SQL is not clear, but these two kinds of writing should be very general,

CodePudding user response:

 
SQL> 
SQL> Create table test (s varchar (10), v int); 
The Table created 
SQL> The begin 
2 insert into the test values (' V ', '41); 
3 the insert into the test values (' C ', '699'); 
4 the insert into the test values (' D ', '19); 
5 the insert into the test values (' J ', '172'); 
6 the end; 
7/
PL/SQL procedure successfully completed 
SQL> A10 col v format; 
SQL> - a large amount of data, the method 2 have advantages 
SQL> Select the s, v, 1.0 v */sum (v) over (V1) from the test; 
S V V1 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
41 0.04403866 V 
C 699 0.75080558 
19 D 0.02040816 
J 172 0.18474758 
SQL> Select the s, v, v * 1.0/(select sum (v) from test) V2 from test; 
S V V2 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
41 0.04403866 V 
C 699 0.75080558 
19 D 0.02040816 
J 172 0.18474758 
SQL> Drop table test purge; 
Table dropped 

SQL>

CodePudding user response:

The sum is not line?

CodePudding user response:

Select a, (b/(select sum (b) the from t)) from t

CodePudding user response:

Where is the data, I can't see

CodePudding user response:

Look at the first, but should be good

CodePudding user response:

Directly to carry out the
AS WITH T (
SELECT the 'V' STATUS, 41 SHULIANG FROM DUAL UNION ALL
SELECT the 'C' STATUS, 699 SHULIANG FROM DUAL UNION ALL
SELECT the 'D' STATUS, 19 SHULIANG FROM DUAL UNION ALL
SELECT the 'J' STATUS, 172 SHULIANG FROM DUAL)
SELECT T. *, SHULIANG/SUM (SHULIANG) OVER (ORDER BY 1) * 100 "hundred (actual value),"
ROUND (SHULIANG/SUM (SHULIANG) OVER (ORDER BY 1) * 100, 2) "hundred (keep two decimal places)" FROM T.

CodePudding user response:

references to peach blossom island, 4/f, huangdao main response:

 
The select ratio_to_report (SHULIANG) over (zb), t. * from t

This is the simplest