[prog] newbie SQL query assistance needed

[Tina]

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
<chris+linuxchix at aptivate.org<chris%2Blinuxchix at aptivate.org>
> wrote:

> On Tue, 27 Apr 2010, Chris Wilson wrote:
> > On Tue, 27 Apr 2010, Tina wrote:
> >
> > > I have an orders table. In it are all the orders (orderid) with each
> item
> > > ordered (itemid) and quantity (quantity).
> > >
> > > I need to figure out which item was ordered the most times. I know it
> > > involves a multi-row query, but I just can't seem to figure it out.
> >
> > select itemid, count(1) from orders group by itemid;
>
> Actually, sorry, I just realised that you only want the first one. To do
> that, use:
>
>  select itemid from orders group by itemid order by count(1) desc
>  limit 1;
>
> Cheers, Chris.
> --
> Aptivate | http://www.aptivate.org | Phone: +44 1223 760887
> The Humanitarian Centre, Fenner's, Gresham Road, Cambridge CB1 2ES
>
> Aptivate is a not-for-profit company registered in England and Wales
> with company number 04980791.
>