sub query

From: "Martin Kuria" <martinkuria(at)hotmail(dot)com>
To: ch(at)rodos(dot)fzk(dot)de, pgsql-sql(at)postgresql(dot)org
Subject: sub query
Date: 2003-09-20 13:40:13
Message-ID: Sea2-F26dpGOjXZ1taz00002ac3@hotmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.) martin(dot)kuria(at)unon(dot)org
+----------------------------------------------------+

>From: Christoph Haller <ch(at)rodos(dot)fzk(dot)de>
>To: pgsql-sql(at)postgresql(dot)org
>CC: martinkuria(at)hotmail(dot)com
>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 majordomo(at)postgresql(dot)org 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Christopher Browne 2003-09-20 14:00:19 Re: auto_increment
Previous Message Richard Huxton 2003-09-20 11:55:53 Re: auto_increment