Distribution Calculation

Hi

Can someone please tell me how I can take a set of sales orders and generate a table or set of data that show the percentage of sales per product which are in different sales ranges

eg Socks - Beige

50% Orders are above $100

20% Orders are above $90 and less than 100

30% Orders are above $60 and less than 90

Many thanks for your time

TV

This will work:

create table sale
(
saleId int identity,
value decimal(10,5)
)
go
insert into sale (value)
select 100
union all
select 110
union all
select 90
union all
select 20
union all
select 30
union all
select 100
union all
select 95
union all
select 35
union all
select 39
union all
select 20
go

select count(*),
sum(case when value >= 100 then 1.0 else 0 end) / count(*) * 100 as [100Up],
sum(case when value < 100 and value >= 90 then 1.0 else 0 end) /
count(*) * 100 as [between90and99.9],
sum(case when value < 100 then 1.0 else 0 end) / count(*) * 100 as [under90]
from sale

|||

Thankyou very much Louis

If I want this distribution per store

do i just add the following to it

select StoreID, count(*),
sum(case when value >= 100 then 1.0 else 0 end) / count(*) * 100 as [100Up],
sum(case when value < 100 and value >= 90 then 1.0 else 0 end) /
count(*) * 100 as [between90and99.9],
sum(case when value < 100 then 1.0 else 0 end) / count(*) * 100 as [under90]
from sale

Group By StoreID

|||That looks right to me.