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

10118419

Select Sum (sl), name From tab Group By name Order By name desc
The second question is a bit unclear. Do you want to calculate those combinations that account for 20%, 50%, and 80% of the total sales? There are many pulls and there is no query, such as 20% or H

恶魔右指

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

To simplify the statement use variables:
declare @sql varchar (2000)
set @sql = '(select model, sales number = sum (sales volume),
Percentage = str (convert (numeric (5, 2), sum (sales)) / (select sum (sales) from ta), 5, 2)
from ta group by model) '
set @ sql = 'select *, (select sum (cast (percent as numeric (5,3))) from' + @ sql
        + 'as tb where percentage!> b. percentage) as decreasing'
        + 'from' + @ sql + 'as b'
set @ sql = 'select model, sales number, percentage from (' + @ sql + ') as ta where decreasing> .5'-the proportion of the column
--print @sql
exec (@sql)

Model Sales Number Percent
---- ----------- -----
E 7 0.35
H 4 0.20
declare @sql varchar (2000)
set @sql = '(select model, sales number = sum (sales volume),
Percentage = str (convert (numeric (5, 2), sum (sales)) / (select sum (sales) from ta), 5, 2)
from ta group by model) '
set @ sql = 'select *, (select sum (cast (percent as numeric (5,3))) from' + @ sql
        + 'as tb where percentage!> b. percentage) as decreasing'
        + 'from' + @ sql + 'as b'
set @ sql = 'select model, sales number, percentage from (' + @ sql + ') as ta where decreasing> .2'-greater than 20% = 80%
--print @sql
exec (@sql)

Model Sales Number Percent
---- ----------- -----
A 3 0.15
C 2 0.10
E 7 0.35
H 4 0.20

lxyxhl

26426613
This is the case, and the result must be that the number is queried from large to small.
you could put it that way:
For example, given a percentage: 60%.
Well, I first look for the highest-selling, E7, but it has not reached 60%, so I must add the second-selling sales (but not other ones), the second-selling sales is H4, they are still It did not reach 12 (60%). So it added three of them, the third named A3, there are 14 of them, and it has reached 60%. The end of the query...