need help with query

[Mike Blanchard]

I'm new to SQL and databases and have a delema which I'm sure is an
easy thing for you experts out there to do. What I have is this: I
have a table similliar to this:

table name: tablename
columns: date, name, purchased

Within that table the following data resides:

date name purchased
1/1/03 Mike 5
2/3/03 John 3
2/3/03 Mike 2
2/4/03 larry 2

etc etc etc.

What I want to do is get the top 5 names, and list how many total
they've purchased for a cirtain date range. I would have to add up
all the dupe purchases for each name for that date range, then produce
the top 5 purchases for that quarter.

I'm stuck on the adding up the dupes part, and kinda shaky on the
rest. I can find the dupes for a date range, but the rest is lost in
a brain cloud for me.

Can anyone help me with this please? I'm using SQL 2000 for the
server, but will be calling the SQL queary within an ASP web page.

thanks all!
Mike B

[WangKhar]

seems a little extreme?

SELECT top 5 purchaser, SUM(quantity) AS Total, count(purchaser ) as Transactions
FROM Purchases
WHERE purchase_date BETWEEN @start_date AND @end_date
GROUP BY purchaser
order by SUM(quantity) desc

would also solve your issue...with that ddl

"John Gilson" <ja*@acm.org> wrote in message news:<PN******* *************@t wister.nyc.rr.c om>...

"Mike Blanchard" <ex****@thelair .com> wrote in message
news:cc******** *************** ***@posting.goo gle.com...

I'm new to SQL and databases and have a delema which I'm sure is an
easy thing for you experts out there to do. What I have is this: I
have a table similliar to this:

table name: tablename
columns: date, name, purchased

Within that table the following data resides:

date name purchased
1/1/03 Mike 5
2/3/03 John 3
2/3/03 Mike 2
2/4/03 larry 2

etc etc etc.

What I want to do is get the top 5 names, and list how many total
they've purchased for a cirtain date range. I would have to add up
all the dupe purchases for each name for that date range, then produce
the top 5 purchases for that quarter.

I'm stuck on the adding up the dupes part, and kinda shaky on the
rest. I can find the dupes for a date range, but the rest is lost in
a brain cloud for me.

Can anyone help me with this please? I'm using SQL 2000 for the
server, but will be calling the SQL queary within an ASP web page.

thanks all!
Mike B

I'm not completely clear as to what you're asking but the following
UDF might be helpful.

CREATE TABLE Purchases
(
purchase_date SMALLDATETIME NOT NULL,
purchaser VARCHAR(25) NOT NULL,
quantity INT NOT NULL,
PRIMARY KEY (purchase_date, purchaser)
)

-- Sample data
INSERT INTO Purchases (purchase_date, purchaser, quantity)
SELECT '20030101', 'Mike', 5
UNION ALL
SELECT '20030203', 'John', 3
UNION ALL
SELECT '20030203', 'Mike', 2
UNION ALL
SELECT '20030204', 'Larry', 2

CREATE FUNCTION TopPurchasers
(@start_date SMALLDATETIME,
@end_date SMALLDATETIME,
@top_n INT)
RETURNS TABLE
AS
RETURN(
SELECT purchaser, total, rank
FROM (SELECT P1.purchaser, P1.total, COUNT(*) AS rank
FROM (SELECT purchaser, SUM(quantity) AS total
FROM Purchases
WHERE purchase_date BETWEEN
@start_date AND @end_date
GROUP BY purchaser) AS P1
INNER JOIN
(SELECT purchaser, SUM(quantity) AS total
FROM Purchases
WHERE purchase_date BETWEEN
@start_date AND @end_date
GROUP BY purchaser) AS P2
ON P2.total >= P1.total
GROUP BY P1.purchaser, P1.total) AS Ranks
WHERE rank <= @top_n
)

-- Top 2 purchasers in Q1 of 2003
SELECT purchaser, total, rank
FROM TopPurchasers(' 20030101', '20030331', 2)
ORDER BY rank

purchaser total rank
Mike 7 1
John 3 2

Regards,
jag

[John Gilson]

"WangKhar" <Wa******@yahoo .com> wrote in message
news:bb******** *************** ***@posting.goo gle.com...

seems a little extreme?

SELECT top 5 purchaser, SUM(quantity) AS Total, count(purchaser ) as Transactions
FROM Purchases
WHERE purchase_date BETWEEN @start_date AND @end_date
GROUP BY purchaser
order by SUM(quantity) desc

would also solve your issue...with that ddl
The solution I gave is parameterized not only by date range but by rank.
If one wants the top 5 today, it's fair to assume that the top 10 will pop up
tomorrow. The only additional code comes from having to determine rank
by other than the use of TOP which, of course, requires that the number of
rows returned be specified as a constant. Using a UDF just allows the
solution to be packaged in a modular reusable form for direct use within a
SELECT statement. After all, just because it's SQL doesn't mean good
software engineering practice needs to go totally out the window. :)

Regards,
jag
"John Gilson" <ja*@acm.org> wrote in message news:<PN******* *************@t wister.nyc.rr.c om>...

"Mike Blanchard" <ex****@thelair .com> wrote in message
news:cc******** *************** ***@posting.goo gle.com...

I'm new to SQL and databases and have a delema which I'm sure is an
easy thing for you experts out there to do. What I have is this: I
have a table similliar to this:

table name: tablename
columns: date, name, purchased

Within that table the following data resides:

date name purchased
1/1/03 Mike 5
2/3/03 John 3
2/3/03 Mike 2
2/4/03 larry 2

etc etc etc.

What I want to do is get the top 5 names, and list how many total
they've purchased for a cirtain date range. I would have to add up
all the dupe purchases for each name for that date range, then produce
the top 5 purchases for that quarter.

I'm stuck on the adding up the dupes part, and kinda shaky on the
rest. I can find the dupes for a date range, but the rest is lost in
a brain cloud for me.

Can anyone help me with this please? I'm using SQL 2000 for the
server, but will be calling the SQL queary within an ASP web page.

thanks all!
Mike B

I'm not completely clear as to what you're asking but the following
UDF might be helpful.

CREATE TABLE Purchases
(
purchase_date SMALLDATETIME NOT NULL,
purchaser VARCHAR(25) NOT NULL,
quantity INT NOT NULL,
PRIMARY KEY (purchase_date, purchaser)
)

-- Sample data
INSERT INTO Purchases (purchase_date, purchaser, quantity)
SELECT '20030101', 'Mike', 5
UNION ALL
SELECT '20030203', 'John', 3
UNION ALL
SELECT '20030203', 'Mike', 2
UNION ALL
SELECT '20030204', 'Larry', 2

CREATE FUNCTION TopPurchasers
(@start_date SMALLDATETIME,
@end_date SMALLDATETIME,
@top_n INT)
RETURNS TABLE
AS
RETURN(
SELECT purchaser, total, rank
FROM (SELECT P1.purchaser, P1.total, COUNT(*) AS rank
FROM (SELECT purchaser, SUM(quantity) AS total
FROM Purchases
WHERE purchase_date BETWEEN
@start_date AND @end_date
GROUP BY purchaser) AS P1
INNER JOIN
(SELECT purchaser, SUM(quantity) AS total
FROM Purchases
WHERE purchase_date BETWEEN
@start_date AND @end_date
GROUP BY purchaser) AS P2
ON P2.total >= P1.total
GROUP BY P1.purchaser, P1.total) AS Ranks
WHERE rank <= @top_n
)

-- Top 2 purchasers in Q1 of 2003
SELECT purchaser, total, rank
FROM TopPurchasers(' 20030101', '20030331', 2)
ORDER BY rank

purchaser total rank
Mike 7 1
John 3 2

Regards,
jag

[Mike Blanchard]

Thank you all for the help. I went with the Sum() As, Group By
solution, which I actually cameacross before my message showed up in
Google :-)

I am going over the code that shows the rankings though, i think
that will be useful in the future for me too!

thank you all again!
Mike B
"John Gilson" <ja*@acm.org> wrote in message news:<SF******* *************@t wister.nyc.rr.c om>...

"WangKhar" <Wa******@yahoo .com> wrote in message
news:bb******** *************** ***@posting.goo gle.com...

seems a little extreme?

SELECT top 5 purchaser, SUM(quantity) AS Total, count(purchaser ) as Transactions
FROM Purchases
WHERE purchase_date BETWEEN @start_date AND @end_date
GROUP BY purchaser
order by SUM(quantity) desc

would also solve your issue...with that ddl

The solution I gave is parameterized not only by date range but by rank.
If one wants the top 5 today, it's fair to assume that the top 10 will pop up
tomorrow. The only additional code comes from having to determine rank
by other than the use of TOP which, of course, requires that the number of
rows returned be specified as a constant. Using a UDF just allows the
solution to be packaged in a modular reusable form for direct use within a
SELECT statement. After all, just because it's SQL doesn't mean good
software engineering practice needs to go totally out the window. :)

Regards,
jag

"John Gilson" <ja*@acm.org> wrote in message news:<PN******* *************@t wister.nyc.rr.c om>...

"Mike Blanchard" <ex****@thelair .com> wrote in message
news:cc******** *************** ***@posting.goo gle.com...
> I'm new to SQL and databases and have a delema which I'm sure is an
> easy thing for you experts out there to do. What I have is this: I
> have a table similliar to this:
>
> table name: tablename
> columns: date, name, purchased
>
> Within that table the following data resides:
>
> date name purchased
> 1/1/03 Mike 5
> 2/3/03 John 3
> 2/3/03 Mike 2
> 2/4/03 larry 2
>
> etc etc etc.
>
> What I want to do is get the top 5 names, and list how many total
> they've purchased for a cirtain date range. I would have to add up
> all the dupe purchases for each name for that date range, then produce
> the top 5 purchases for that quarter.
>
> I'm stuck on the adding up the dupes part, and kinda shaky on the
> rest. I can find the dupes for a date range, but the rest is lost in
> a brain cloud for me.
>
> Can anyone help me with this please? I'm using SQL 2000 for the
> server, but will be calling the SQL queary within an ASP web page.
>
> thanks all!
> Mike B

I'm not completely clear as to what you're asking but the following
UDF might be helpful.

CREATE TABLE Purchases
(
purchase_date SMALLDATETIME NOT NULL,
purchaser VARCHAR(25) NOT NULL,
quantity INT NOT NULL,
PRIMARY KEY (purchase_date, purchaser)
)

-- Sample data
INSERT INTO Purchases (purchase_date, purchaser, quantity)
SELECT '20030101', 'Mike', 5
UNION ALL
SELECT '20030203', 'John', 3
UNION ALL
SELECT '20030203', 'Mike', 2
UNION ALL
SELECT '20030204', 'Larry', 2

CREATE FUNCTION TopPurchasers
(@start_date SMALLDATETIME,
@end_date SMALLDATETIME,
@top_n INT)
RETURNS TABLE
AS
RETURN(
SELECT purchaser, total, rank
FROM (SELECT P1.purchaser, P1.total, COUNT(*) AS rank
FROM (SELECT purchaser, SUM(quantity) AS total
FROM Purchases
WHERE purchase_date BETWEEN
@start_date AND @end_date
GROUP BY purchaser) AS P1
INNER JOIN
(SELECT purchaser, SUM(quantity) AS total
FROM Purchases
WHERE purchase_date BETWEEN
@start_date AND @end_date
GROUP BY purchaser) AS P2
ON P2.total >= P1.total
GROUP BY P1.purchaser, P1.total) AS Ranks
WHERE rank <= @top_n
)

-- Top 2 purchasers in Q1 of 2003
SELECT purchaser, total, rank
FROM TopPurchasers(' 20030101', '20030331', 2)
ORDER BY rank

purchaser total rank
Mike 7 1
John 3 2

Regards,
jag