|
|
Posted by Ed Murphy on 10/18/06 15:49
Bill wrote:
> kansaskannan@gmail.com wrote:
>> I have an Access 2000 database which lists customers by name, and how
>> much (in dollars) they have purchased of various products. How do I
>> write a SQL statement to select customers who make up the top 60% of
>> total sales dollars? I need to have a list of customers returned.
>>
>> The list of customers that get returned will be used to select other
>> data from the same database. (I mention this in case there are multiple
>> ways of getting the customer list).
> Try something of the format:
>
> SELECT cs.cus_id, cs.cus_name, sum(sales) as sales
> FROM cus_sales cs
> INNER JOIN ( SELECT sum(sales) tot_sales FROM cus_sales ) ts
> ON cs.sales >= ts.tot_sales * .6
[top-posting fixed]
1) "GROUP BY cs.cus_id, cs.cus_name" is missing
2) This would only return data if a single customer accounts for
60% or more of total sales
3) "sum(sales) as sales" is probably a bad idea
4) cus_name should be in customers, not cus_sales
I think the following would work. Perhaps someone can collapse this
into a single query, suitable for stuffing into a view.
create table #t1 (
cus_id int,
tot_sales_cus decimal(15,2),
cus_sales_rank int,
tot_sales_running decimal(15,2)
)
insert into #t1 (cus_id, tot_sales_cus)
select cus_id, sum(sales)
from cus_sales
group by cus_id
update #t1 as x
set cus_sales_rank = 1 + (
select count(*)
from #t1 as y
where y.tot_sales_cus > x.tot_sales_cus
)
update #t1 as x
set tot_sales_running = (
select sum(tot_sales_cus)
from #t1 as y
where y.cus_sales_rank <= x.cus_sales_rank)
declare @tot_sales_company decimal(15,2)
select @tot_sales_company = (select sum(sales) from cus_sales)
select x.cus_id, c.cus_name, x.tot_sales_cus
from tot_sales_running as x
join cus_sales as c
where 100 * x.tot_sales_cus / @tot_sales_company >= 60
Navigation:
[Reply to this message]
|