Bill wrote:
ka**********@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
