pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsub query
8+ messages / 3 participants
[nested] [flat]
* sub query
@ 2003-09-17 08:18 Martin Kuria <martinkuria@hotmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Martin Kuria @ 2003-09-17 08:18 UTC (permalink / raw)
To: pgsql-sql
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.
Kind regards
+-----------------------------------------------------+
| Martin W. Kuria (Mr.) martin.kuria@unon.org
+----------------------------------------------------+
_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
^ permalink raw reply [nested|flat] 8+ messages in thread
* sub query
@ 2003-09-17 08:19 Martin Kuria <martinkuria@hotmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Martin Kuria @ 2003-09-17 08:19 UTC (permalink / raw)
To: pgsql-sql
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.
Kind regards
+-----------------------------------------------------+
| Martin W. Kuria (Mr.) martin.kuria@unon.org
+----------------------------------------------------+
_________________________________________________________________
Tired of spam? Get advanced junk mail protection with MSN 8.
http://join.msn.com/?page=features/junkmail
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: sub query
@ 2003-09-17 08:38 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 8+ messages in thread
From: Christoph Haller @ 2003-09-17 08:38 UTC (permalink / raw)
To: pgsql-sql; +Cc: martinkuria@hotmail.com
> 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.
Regards, Christoph
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: sub query
@ 2003-09-17 08:54 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 8+ messages in thread
From: Christoph Haller @ 2003-09-17 08:54 UTC (permalink / raw)
To: pgsql-sql; +Cc: martinkuria@hotmail.com
>
> > 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: sub query
@ 2003-09-17 09:57 Martin Kuria <martinkuria@hotmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Martin Kuria @ 2003-09-17 09:57 UTC (permalink / raw)
To: ch@rodos.fzk.de; pgsql-sql
Thanks Haller, the second one worked thanks a million be blessed
Regards
+-----------------------------------------------------+
| Martin W. Kuria (Mr.) martin.kuria@unon.org
+----------------------------------------------------+
>From: Christoph Haller <ch@rodos.fzk.de>
>To: pgsql-sql@postgresql.org
>CC: martinkuria@hotmail.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@postgresql.org so that your
> message can get through to the mailing list cleanly
_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: sub query
@ 2003-09-17 12:00 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 8+ messages in thread
From: Christoph Haller @ 2003-09-17 12:00 UTC (permalink / raw)
To: pgsql-sql; +Cc: martinkuria@hotmail.com
The reason why the first one
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;
did not work is because the query needs a
ORDER BY 1 DESC LIMIT 1 like the second one.
Mind the DESCending order.
Regards, Christoph
>
> Thanks Haller, the second one worked thanks a million be blessed
>
> > >
> > > > 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;
> >
^ permalink raw reply [nested|flat] 8+ messages in thread
* sub query
@ 2003-09-20 13:40 Martin Kuria <martinkuria@hotmail.com>
2003-09-20 15:38 ` Re: sub query Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 8+ messages in thread
From: Martin Kuria @ 2003-09-20 13:40 UTC (permalink / raw)
To: ch@rodos.fzk.de; 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.kuria@unon.org
+----------------------------------------------------+
>From: Christoph Haller <ch@rodos.fzk.de>
>To: pgsql-sql@postgresql.org
>CC: martinkuria@hotmail.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@postgresql.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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: sub query
2003-09-20 13:40 sub query Martin Kuria <martinkuria@hotmail.com>
@ 2003-09-20 15:38 ` Peter Eisentraut <peter_e@gmx.net>
0 siblings, 0 replies; 8+ messages in thread
From: Peter Eisentraut @ 2003-09-20 15:38 UTC (permalink / raw)
To: Martin Kuria <martinkuria@hotmail.com>; +Cc: pgsql-sql
Martin Kuria writes:
> 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
Add o.item_status to the GROUP BY clause.
--
Peter Eisentraut peter_e@gmx.net
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-09-20 15:38 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-17 08:18 sub query Martin Kuria <martinkuria@hotmail.com>
2003-09-17 08:19 sub query Martin Kuria <martinkuria@hotmail.com>
2003-09-17 08:38 Re: sub query Christoph Haller <ch@rodos.fzk.de>
2003-09-17 08:54 Re: sub query Christoph Haller <ch@rodos.fzk.de>
2003-09-17 09:57 Re: sub query Martin Kuria <martinkuria@hotmail.com>
2003-09-17 12:00 Re: sub query Christoph Haller <ch@rodos.fzk.de>
2003-09-20 13:40 sub query Martin Kuria <martinkuria@hotmail.com>
2003-09-20 15:38 ` Peter Eisentraut <peter_e@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox