need help with query

**WangKhar** · Jul 20 '05, 12:50 AM

Re: need help with query

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" <jag@acm.org> wrote in message news:<PNHMa.355 7$351.1335124@t wister.nyc.rr.c om>...[color=blue]
> "Mike Blanchard" <exibar@thelair .com> wrote in message
> news:cc0872df.0 307021003.34b46 423@posting.goo gle.com...[color=green]
> > 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[/color]
>
> 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[/color]

**John Gilson** · Jul 20 '05, 12:50 AM

Re: need help with query

"WangKhar" <Wangkhar@yahoo .com> wrote in message
news:bb269444.0 307040133.6a628 4fd@posting.goo gle.com...[color=blue]
> 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[/color]

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
[color=blue]
> "John Gilson" <jag@acm.org> wrote in message news:<PNHMa.355 7$351.1335124@t wister.nyc.rr.c om>...[color=green]
> > "Mike Blanchard" <exibar@thelair .com> wrote in message
> > news:cc0872df.0 307021003.34b46 423@posting.goo gle.com...[color=darkred]
> > > 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[/color]
> >
> > 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[/color][/color]

**Mike Blanchard** · Jul 20 '05, 12:50 AM

Re: need help with query

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" <jag@acm.org> wrote in message news:<SFeNa.613 7$351.2053487@t wister.nyc.rr.c om>...[color=blue]
> "WangKhar" <Wangkhar@yahoo .com> wrote in message
> news:bb269444.0 307040133.6a628 4fd@posting.goo gle.com...[color=green]
> > 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[/color]
>
> 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
>[color=green]
> > "John Gilson" <jag@acm.org> wrote in message news:<PNHMa.355 7$351.1335124@t wister.nyc.rr.c om>...[color=darkred]
> > > "Mike Blanchard" <exibar@thelair .com> wrote in message
> > > news:cc0872df.0 307021003.34b46 423@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[/color][/color][/color]

need help with query

need help with query

Comment

Comment

Comment