10 Replies - 1222 Views - Last Post: 31 January 2009 - 10:25 AM Rate Topic: -----

#1 Fusion.01   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 52
  • Joined: 17-January 09

SQL HELP!

Posted 30 January 2009 - 08:12 AM

i'm trying to retrieve the top 10 best selling album from my database.however if there's 2 similiar album with the same amount sold, both will be retrieved resulting in 11 object being passed out.this is the code.but i'm not sure if this is the correct sql to pass in
SELECT TOP 10 ProductID, SUM(totalQtySold) FROM OrderDetails GROUP BY ProductID ORDER BY SUM(totalQtySold)DESC

Is This A Good Question/Topic? 0
  • +

Replies To: SQL HELP!

#2 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7419
  • View blog
  • Posts: 15,373
  • Joined: 16-October 07

Re: SQL HELP!

Posted 30 January 2009 - 08:41 AM

Looks ok. Perhaps you want something like this:

SELECT top 10 a.*, b.Sold
	FROM OrderDetails a
		inner join (
			SELECT ProductID, SUM(totalQtySold) as Sold FROM OrderDetails GROUP BY ProductID
		) b on a.ProductID=b.ProductID
	ORDER BY b.sold


Was This Post Helpful? 0
  • +
  • -

#3 mostyfriedman   User is offline

  • The Algorithmi
  • member icon

Reputation: 729
  • View blog
  • Posts: 4,473
  • Joined: 24-October 08

Re: SQL HELP!

Posted 30 January 2009 - 09:01 AM

i thought this was the java forum
Was This Post Helpful? 0
  • +
  • -

#4 Fusion.01   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 52
  • Joined: 17-January 09

Re: SQL HELP!

Posted 30 January 2009 - 10:26 AM

if i wanna retrieve the top 10 best selling album from my data base is this the correct sql?and if there is 2 product of the same sales figure i only want one of it..is it something like this?

SELECT TOP 10 ProductID, SUM(totalQtySold) FROM OrderDetails GROUP BY ProductID ORDER BY SUM(totalQtySold)DESC

Was This Post Helpful? 0
  • +
  • -

#5 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7419
  • View blog
  • Posts: 15,373
  • Joined: 16-October 07

Re: SQL HELP!

Posted 30 January 2009 - 10:50 AM

:blink: I posted the answer to this this morning. http://www.dreaminco...wtopic83674.htm

Double posting is bad enough. Not even reading the responses puts you firmly in the "nevermind" category. :angry:
Was This Post Helpful? 0
  • +
  • -

#6 johnmalloy   User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 40
  • Joined: 24-September 08

Re: SQL HELP!

Posted 30 January 2009 - 02:26 PM

Limit 10
this will limit only 10 records being returned
Was This Post Helpful? 0
  • +
  • -

#7 darkeclipse   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 30-January 09

Re: SQL HELP!

Posted 30 January 2009 - 11:21 PM

View Postbaavgai, on 30 Jan, 2009 - 09:50 AM, said:

:blink: I posted the answer to this this morning. http://www.dreaminco...wtopic83674.htm

Double posting is bad enough. Not even reading the responses puts you firmly in the "nevermind" category. :angry:


its not his fault...his helping me to post...by the way your code returned me the 10 readings but not from the most totalQtySold to the least. Instead they returned me from the least sold to the most sold.

View Postjohnmalloy, on 30 Jan, 2009 - 01:26 PM, said:

Limit 10
this will limit only 10 records being returned


i tried and kept getting Syntax Error in ORDER by Clause.
Was This Post Helpful? 0
  • +
  • -

#8 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: SQL HELP!

Posted 30 January 2009 - 11:22 PM

Topics merged, please don't create duplicate topics :)

As far as them being in the wrong order, simply change the ORDER BY Clause. Also, sometimes when it's something as small as being in the wrong order you can do some research on your own as well, you cant always expect people to do all your work for you :)

SELECT top 10 a.*, b.Sold
	 FROM OrderDetails a
		  inner join (
			   SELECT ProductID, SUM(totalQtySold) as Sold FROM OrderDetails GROUP BY ProductID
		  ) b on a.ProductID=b.ProductID
	 ORDER BY b.sold DESC


Was This Post Helpful? 0
  • +
  • -

#9 darkeclipse   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 30-January 09

Re: SQL HELP!

Posted 31 January 2009 - 03:28 AM

View PostPsychoCoder, on 30 Jan, 2009 - 10:22 PM, said:

Topics merged, please don't create duplicate topics :)

As far as them being in the wrong order, simply change the ORDER BY Clause. Also, sometimes when it's something as small as being in the wrong order you can do some research on your own as well, you cant always expect people to do all your work for you :)

SELECT top 10 a.*, b.Sold
	 FROM OrderDetails a
		  inner join (
			   SELECT ProductID, SUM(totalQtySold) as Sold FROM OrderDetails GROUP BY ProductID
		  ) b on a.ProductID=b.ProductID
	 ORDER BY b.sold DESC




i did tried it myself for quite long and really did not know how to do it...hmm...your code gives mi the correct order but i gt back extra again as the amount of the last 2 totalQtySold is the same. my data and what is returned after query are in the attachment...please take a look.

Attached File(s)

  • Attached File  1.txt (783bytes)
    Number of downloads: 72

This post has been edited by darkeclipse: 31 January 2009 - 03:34 AM

Was This Post Helpful? 0
  • +
  • -

#10 Auzzie   User is offline

  • D.I.C Addict
  • member icon

Reputation: 43
  • View blog
  • Posts: 573
  • Joined: 20-January 09

Re: SQL HELP!

Posted 31 January 2009 - 05:16 AM

maybe at the end of the SQL type limit 0,10
Was This Post Helpful? 0
  • +
  • -

#11 darkeclipse   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 30-January 09

Re: SQL HELP!

Posted 31 January 2009 - 10:25 AM

View PostAuzzie, on 31 Jan, 2009 - 04:16 AM, said:

maybe at the end of the SQL type limit 0,10


hmm...i'm using MS Query so i don't think limit can be used but anyway i tried and got syntax error in ORDER By clause. When i remove DESC and just put limit at the ending, it wont work either.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1