[prog] newbie SQL query assistance needed

Tue Apr 27 22:31:15 UTC 2010

Not 100% sure cuz I am on my phone rigt now but this should be what you need or close to it.

Select itemid, sum(quantity) as total from orders group by itemid order by total

-- Sent from my Palm Pre
On Apr 27, 2010 3:35 PM, Tina &lt;tyamar at gmail.com&gt; wrote: 

I actually got as far as this:

select itemid, count((itemid)*quantity)

     from orders

     group by itemid

That showed the total number of times each item was ordered, which is great.

But now I need to take that listing and figure out the MAX (which itemid has

the most in the new column).

I tried to do:

select itemid, max(total)

from orders

where itemid IN (select count((itemid)*quantity "total")

     from orders

     group by itemid)

order by itemid

But it's not liking me trying to use that total field that was created in

that instance.

-- Tina

On Tue, Apr 27, 2010 at 15:17, Chris Wilson

&lt;chris+linuxchix at aptivate.org&lt;chris%2Blinuxchix at aptivate.org&gt;

&gt; wrote:

&gt; On Tue, 27 Apr 2010, Chris Wilson wrote:

&gt; &gt; On Tue, 27 Apr 2010, Tina wrote:

&gt; &gt;

&gt; &gt; &gt; I have an orders table. In it are all the orders (orderid) with each

&gt; item

&gt; &gt; &gt; ordered (itemid) and quantity (quantity).

&gt; &gt; &gt;

&gt; &gt; &gt; I need to figure out which item was ordered the most times. I know it

&gt; &gt; &gt; involves a multi-row query, but I just can't seem to figure it out.

&gt; &gt;

&gt; &gt; select itemid, count(1) from orders group by itemid;

&gt;

&gt; Actually, sorry, I just realised that you only want the first one. To do

&gt; that, use:

&gt;

&gt;  select itemid from orders group by itemid order by count(1) desc

&gt;  limit 1;

&gt;

&gt; Cheers, Chris.

&gt; --

&gt; Aptivate | http://www.aptivate.org | Phone: +44 1223 760887

&gt; The Humanitarian Centre, Fenner's, Gresham Road, Cambridge CB1 2ES

&gt;

&gt; Aptivate is a not-for-profit company registered in England and Wales

&gt; with company number 04980791.

&gt;

_______________________________________________

Programming mailing list

Programming at linuxchix.org

http://mailman.linuxchix.org/mailman/listinfo/programming