By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,376 Members | 1,566 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,376 IT Pros & Developers. It's quick & easy.

sql statement to select customers contributing top 60% of sales

P: n/a
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).
Thank you for any advice.

Oct 18 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

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).
Thank you for any advice.
Oct 18 '06 #2

P: n/a
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
Oct 18 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.