how to get cumulative total descending

16 Jul 2005 4:43 AM

NHUMAUOXPJHS

I have customerid and sales and calculated sales percentage.

What is the best way to calculate the cumulative sales and cumulative
percentage
how do I get the cumulative sales and cumulative sales percentage.

-- drop table test_sales1
create table test_sales1
(customerid int
,salestot decimal(12,3)
,sales_pct decimal(12,3)
,cumulative_sales decimal(12,3)
,cumulative_pct decimal(12,3)
)

insert into test_sales1 values(116, 460, 23, 0, 0);
insert into test_sales1 values(101, 240, 12, 0, 0);
insert into test_sales1 values(101, 160, 8, 0, 0);
insert into test_sales1 values(105, 140, 7, 0, 0);
insert into test_sales1 values(120, 600, 30, 0, 0);
insert into test_sales1 values(115, 160, 8, 0, 0);
insert into test_sales1 values(114, 100, 5, 0, 0);
insert into test_sales1 values(117, 60, 3, 0, 0);
insert into test_sales1 values(119, 40, 2, 0, 0);
insert into test_sales1 values(113, 20, 1, 0, 0);
insert into test_sales1 values(118, 10, 0.5, 0, 0);
insert into test_sales1 values(111, 6, 0.3, 0, 0);
insert into test_sales1 values(112, 4, 0.2, 0, 0);

select customerid,salestot,sales_pct,cumulative_sales, cumulative_pct
from test_sales1
order by salestot desc

-- drop table test_sales1

16 Jul 2005 5:32 AM

Jens Süßmeyer

Don´t know if that makes sense to sum up the percentage, but thjats the deal
for the cumulative values per Customers:

Select customerid,SUM(salestot) as Sales_Total, SUM(sales_pct) as
Sales_Percentage
From test_sales1
Group by CustomerID

HTH, Jens Suessmeyer.

<NHUMAUOXPJHS@spammotel.com> wrote in message
Show quote

news:eMJznDciFHA.3164@TK2MSFTNGP15.phx.gbl...
>I have customerid and sales and calculated sales percentage.
>
> What is the best way to calculate the cumulative sales and cumulative
> percentage
> how do I get the cumulative sales and cumulative sales percentage.
>
>
>
> -- drop table test_sales1
> create table test_sales1
> (customerid int
> ,salestot decimal(12,3)
> ,sales_pct decimal(12,3)
> ,cumulative_sales decimal(12,3)
> ,cumulative_pct decimal(12,3)
> )
>
> insert into test_sales1 values(116, 460, 23, 0, 0);
> insert into test_sales1 values(101, 240, 12, 0, 0);
> insert into test_sales1 values(101, 160, 8, 0, 0);
> insert into test_sales1 values(105, 140, 7, 0, 0);
> insert into test_sales1 values(120, 600, 30, 0, 0);
> insert into test_sales1 values(115, 160, 8, 0, 0);
> insert into test_sales1 values(114, 100, 5, 0, 0);
> insert into test_sales1 values(117, 60, 3, 0, 0);
> insert into test_sales1 values(119, 40, 2, 0, 0);
> insert into test_sales1 values(113, 20, 1, 0, 0);
> insert into test_sales1 values(118, 10, 0.5, 0, 0);
> insert into test_sales1 values(111, 6, 0.3, 0, 0);
> insert into test_sales1 values(112, 4, 0.2, 0, 0);
>
> select customerid,salestot,sales_pct,cumulative_sales, cumulative_pct
> from test_sales1
> order by salestot desc
>
> -- drop table test_sales1
>
>
>
>
>