[prog] newbie SQL query assistance needed

[Tina]

I figured out the answer:

SELECT itemid FROM orders GROUP BY itemid HAVING SUM(quantity) = (SELECT
MAX(COUNT(*)) FROM orders GROUP BY itemid)

It's doing something weird, though. The "SELECT MAX(COUNT(*)) FROM orders
GROUP BY itemid" portion is giving me back 7, when it should be 8. So, the
parent query is giving me the wrong itemid. Instead of the one that is a
total of 8 orders, it's giving me the one that's only a total of 7. Not sure
why it's doing that.

-- Tina


On Tue, Apr 27, 2010 at 17:31, sangyoon1024 at gmail.com <
sangyoon1024 at gmail.com> wrote:

> 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 <tyamar at gmail.com> 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
> <chris+linuxchix at aptivate.org <chris%2Blinuxchix at aptivate.org><
> chris%2Blinuxchix at aptivate.org <chris%252Blinuxchix 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.
> >
> _______________________________________________
> Programming mailing list
> Programming at linuxchix.org
> http://mailman.linuxchix.org/mailman/listinfo/programming
>