Urgent, regarding the ABC analysis method, find the product name that accounts for 20% of sales, as follows:

lxyxhl

There is a table with the fields: product name, sales quantity, sales (goodsname, salesnum) ...
Find out which products account for 20% of sales.

26426613

declare @ s1 float
select @ s1 = sum (salesum) from sales
select goodsname, sum (salesum) from sales group by goodsname with sum (salesum) / @ sq> = 0.2

lxyxhl

Thank you upstairs, but there are errors:
'sum' is not a recognized GROUP BY option.

lxyxhl

I mean: find out which products are sold from high to low, which account for 20% (may be some products). The sales volume of it should be sorted.

winoudzhang

select goodsname from [table] group by goodsname having (sum (salesnum)
/ (select sum (salesnum) from [table])) = 0.2

fanfan1980

Paste the data and the desired result set

fanfan1980

--such

create table T_sales (goodsname nvarchar (10), salesnum int)
insert T_sales select 'AA', 200
insert T_sales select 'AA', 100
insert T_sales select 'BB', 200

select goodsname,
Sales volume = rtrim (sum (salesnum) * 100 / (select sum (salesnum) from T_sales)) + '%' from T_sales group by goodsname

--result
goodsname sales
---------- -------------
AA 60%
BB 40%

(2 row (s) affected)

lxyxhl

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 are selling the best. Therefore, I can find and sort.
The resulting result set is: (regardless of date).
Model 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 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 ...

lxyxhl

In fact, that is, first group and sort by model (in descending order),
Then, from high to low, look at their quantity and what commodities are when they account for 50% ...
So, I can know which products are selling the best ...

fanfan1980

create table T_sales (goodsname nvarchar (10), salesnum int)
insert T_sales select 'A', 2
union all select 'B', 1
union all select 'C', 1
union all select 'E', 3
union all select 'F', 1
union all select 'H', 2
union all select 'I', 1
union all select 'A', 1
union all select 'C', 1
union all select 'D', 1
union all select 'E', 4
union all select 'H', 2

select goodsname,
Sales volume = sum (salesnum),
[Sales%] = rtrim (sum (salesnum) * 100 / (select sum (salesnum) from T_sales)) + '%' from T_sales group by goodsname
order by sum (salesnum) desc
--result
goodsname sales volume sales volume%
---------- ----------- -------------
E 7 35%
H 4 20%
A 3 15%
C 2 10%
D 1 5%
B 1 5%
I 1 5%
F 1 5%

(8 row (s) affected)