Find the sum of the sales of goods (sorted by name) in a month, sort from high to low, and then count which of the sum o

lxyxhl

See title,
The sample data is as follows:
The format is as follows:
The fields are:
Date, model, sales
12.1 A 2
12.1 B 1
12.1 C 1
12.1 E 3
12.1 F 1
12.1 H 2
12.1 I 1
12.2 A 1
12.2 C 1
12.2 D 1
12.2 E 4
12.2 H 2

A total of 20 units were sold above (regardless of model) .. I want to know which products sell the best. Therefore, I can find them and sort them.
The resulting result set is: (regardless of date).
Model, sales number
E 7
H 4
A 3
C 2
B 1
D 1
F 1
I 1
Now, I want to know, which products accounted for the total sales: 20%, it is obvious that it is E 7, if I want to know which sales accounted for 50%, it is obvious, it is: E 7 ,, H 4 ,, if you want to reach 80%, that is E, H, A, C ..

恶魔右指

select model, sales number = sum (sales volume) from table name group by model order by sales number desc

恶魔右指

select model, sales number = sum (sales volume), percentage = sum (sales volume) / (select sum (sales volume) from table name)
 from table name group by model order by sales number desc

cv4bf5d65fd

select model, sum (sales) 'sale number' from tablename group by model order by sum (sales) desc

我是杀人狂

select model, sum (sales) as sales number from tablename group by model order by sales number desc, model

恶魔右指

select model, sales number = sum (sales volume), percentage = sum (sales volume) / (select sum (sales volume) from table name)
 from table name group by model having sum (sales volume) / (select sum (sales volume) from table name)>. 2-- greater than 20%
order by sales number desc
Just use having definition

lxyxhl

Big brother upstairs, you don't understand what I mean, I mean:
Which commodities account for 20% of the sales volume, it is obvious: E, reached 35%, I just extract E. In your way, E, H will be proposed.
If I want which products account for 50% of sales, in your way, no, because there is no single sales greater than or equal to 10 units.
However, the sum of E and H is 11, which accounts for 50%. I want to extract them.
If I want to reach 80%, then it will be: E, H, A, C, their combined is 16. Meet my requirements ...

恶魔右指

create table ta (date varchar (5), model varchar (2), sales volume int)
insert ta
select '12 .1 ',' A ', 2
union all select '12 .1 ',' B ', 1
union all select '12 .1 ',' C ', 1
union all select '12 .1 ',' E ', 3
union all select '12 .1 ',' F ', 1
union all select '12 .1 ',' H ', 2
union all select '12 .1 ',' I ', 1
union all select '12 .2 ',' A ', 1
union all select '12 .2 ',' C ', 1
union all select '12 .2 ',' D ', 1
union all select '12 .2 ',' E ', 4
union all select '12 .2 ',' H ', 2

select model, sales number = sum (sales volume), sales number = sum (sales volume),
Percentage = str (convert (numeric (5, 2), sum (sales)) / (select sum (sales) from ta), 5, 2)-convert integer
from ta group by model
having str (convert (numeric (5,2), sum (sales)) / (select sum (sales) from ta), 5,2) =. 35--define the expression equal to 35%
order by sales number desc

Model Sales Number Sales Number Percent
---- ----------- ----------- -----
E 7 7 0.35

(The number of rows affected is 1 row)

lxyxhl

Hello upstairs brother, if you change .35 to: .20 or other number less than .35, it will only display one,
If you change .35 to .50, it should display: two, E, H, the number of the two of them has reached 50%, and the latter will not be displayed.
If you change .35 to .80, it should display: four lines: E, H, A, C ... Only by adding the number of C, it will reach: 80%, so it must be displayed to C ...

Is it right? To get this result, you must use stored procedures, or other methods, I feel that a SQL statement is uncertain ...

26426613

A total of 20 units were sold above (regardless of model). I want to know which products are selling best. Therefore, I can find and sort.
The resulting result set is: (regardless of date).
Model number, sales number
E 7
H 4
A 3
C 2
B 1
D 1
F 1
I 1
Now, I want to know, which products I sold accounted for the total sales: 20%, obviously, it is E 7, if I want to know which sales accounted for 50%, obviously, it is: E 7 ,, H 4, If you want to reach 80%, it is E, H, A, C ...

Personally think that this result is uncertain, take
If I want to know what accounts for 50% of the sales volume, it is obvious that it is: E 7 ,, H 4, for E7, A3, B2
How do you determine the content of the goods you want to obtain! Sorting by size is not reasonable! The demand is not clear!