[SQL] sub query

[Martin Kuria]

Hi again I have this problem when I try to run this query, how can I rewrite 
it for it to work.

SELECT o.item_order_num,SUM(o.item_quantity + o.item_product_id) As total, 
o.item_status
FROM soko_product p, soko_ordered_item o
WHERE p.product_id = o.item_product_id
GROUP BY o.item_order_num
ORDER BY o.item_order_num;

Error: Attribute o.item_status must be GROUPED OR USE IN an aggregate 
function

Please do advice thanks again
Kind regards
+-----------------------------------------------------+
| Martin W. Kuria (Mr.) [EMAIL PROTECTED]
+----------------------------------------------------+



From: Christoph Haller <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
CC: [EMAIL PROTECTED]
Subject: Re: [SQL] sub query
Date: Wed, 17 Sep 2003 10:54:49 +0200
>
> > Hi I have this problem, when I try to run this query:
> >
> > SELECT MAX(d), host_position FROM (SELECT host_position,
> > COUNT(host_position) as d FROM sss_host GROUP BY host_position) as
e;
> >
> > am getting and ERROR: Attribute e.host_position must be GROUPed or
> used in
> > an aggregate function.
> >
> > Please to advice what could be the problem and how can I rewrite it
to
> work
> > thanks in advance.
> >
> As the error message says: e.host_position must be GROUPed
>
> so (supposing you want a one row result showing the maximum count)
>
> SELECT MAX(e.d), e.host_position FROM (SELECT host_position,
> COUNT(host_position) as d FROM sss_host GROUP BY host_position) as e
> GROUP BY e.host_position ORDER BY 1 LIMIT 1;
>
> should match your intentions.
>
Just thought about another (less complex) way:
SELECT COUNT(host_position), host_position FROM
sss_host GROUP BY host_position ORDER BY 1 DESC LIMIT 1;
Regards, Christoph



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly
_________________________________________________________________
Add photos to your e-mail with MSN 8. Get 2 months FREE*. 
http://join.msn.com/?page=features/featuredemail

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to [EMAIL PROTECTED] so that your
     message can get through to the mailing list cleanly