Lets say I had to find the products for which we had a total of $500 or more
in sales. I need to see the following information:

· product number

· total sales (in dollars)

· product name



So.I write this..



SQL> select orderdetails.prodnum,sum(unit*price)

2 from products,orderdetails

3 where products.prodnum=orderdetails.prodnum

4 having sum(unit*price) > 500

5 group by orderdetails.prodnum;



And it produces the correct output:



PRODNUM SUM(UNIT*PRICE)

---------- -----------

1267 968.58

1794 600

1239 30344.13

2711 399.95



Given this, how can I find the product that had the highest total sales (you
can use

your answer to the "easy" part). Output should include the same as for the
previous part, with exactly the following:



PRODNUM TOTAL

-------- ----------

1239 30344.13



Could someone help me with this? It's just a practice exercise, but I don't
know how to write the query..If you require more info, please let me know.



Thank you in advance for reading, and for your help...

Baba Boohi wrote:
> Given this, how can I find the product that had the highest total sales (you
> can use your answer to the "easy" part).

This sounds suspiciously like a homework question. Keep in mind that it
is frowned upon to get usenet folks to do your homework for you.

Also, your subject line indicates that this is somehow related to
Oracle. You are posting this question to a MySQL newsgroup. Though the
question isn't specific to either Oracle or MySQL. What's up?

Regards,
Bill K.

I, too, think that I have seen this same question many times in various
SQL related newsgroups. If you are unable to understand these simple
concepts for finding maximum values - you are in deep trouble and
should probably consider an alternative line of work/study.

onedbguru@firstdbasource.com wrote:
> I, too, think that I have seen this same question many times in
> various SQL related newsgroups. If you are unable to understand
> these simple concepts for finding maximum values - you are in deep
> trouble and should probably consider an alternative line of
> work/study.

Such arrogance. You assume a lot.

"Better to keep your mouth shut and appear a fool, than to open it and
remove all doubt....."

- Mark Twain.