Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL

Status
Not open for further replies.

GUJUm0deL

Programmer
Jan 16, 2001
3,676
US
How would you write a query for this: Name of customers who made more then 10 purchases last quarter??
I think this is how to start:

SELECT name, MAX(qty_sold) AS sold
FROM fact f, products p, sales s, date d
WHERE ???????????????????????????????
AND d.date = 032002
[color]
I know this will return ALL max qty_sold...but I only want the top 10...what am I missing??
Thanks... I have not failed; I have merely found 100,000 different ways of not succeding...
 
Hi,

Do you want the top 10 customers, or the customers who have made more than 10 purchases?

J
 
GUJUm0deL,

The following SQL will give u all persons who have bought more than 10 quantities.
Code:
SELECT 
	name, 
	MAX(qty_sold) 
AS 
	sold
FROM 
	fact f, 
	products p, 
	sales s, 
	date d
WHERE 
	place all the joins here
AND 	d.date = 032002

GROUP BY
	name
HAVING
	MAX(qty_sold) 		>	10
[code]

but if u want Top 10 customer names then maybe u should  use Rank function... which RDBMS are u working on????

Sridharan
 
Hello GUJUm0deL,

1. customers having more than 10 purchases.

If you relate this to quantities only (and not to the number of individual orders):

Select customer, sum(qty)
from..............
having sum(qty) > 10
group by customer


2. the top 10 customers based on purchased quantity

Select customer, sum(qty)
from .................
group by customer
order by sum(qty) desc
fetch first 10 rows only

(this is the DB2 variant)

in ORACLE 8 or higher:

Select * from
(Select customer, sum(qty)
from .................
group by customer
order by sum(qty) desc)
where rownum < 11

Prior to ORACLE 8 you would need to use a cursor with PL/SQL as described in ORACLE 8 forum: thread185-114597 T. Blom
Information analyst
tbl@shimano-eu.com
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top