[SQL] sub query
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.) [EMAIL PROTECTED] ++ _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] sub query
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.) [EMAIL PROTECTED] ++ _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] sub query
Thanks Haller, the second one worked thanks a million be blessed 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 _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] sub query
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
[SQL] search facilities
Hi, I have a content management system, for my database driven website developed using php and postgresql but I don't know how to develop a search facility for it. How do I go about it?. I have seen database driven website developed using php and postgresql and they have their built search facilities where can I learn to develop my customized search facility please do advice. Kind Regards +-+ | Martin W. Kuria (Mr.) [EMAIL PROTECTED] ++ _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] search facilities
Bartunov, Thanks alot for you reply, please clarify for me something; I have a database driven website developed using php and postgresql database, can I use tsearch facility and customize it to search my database driven website like how postgresql.org is search please do advice thanks again Kind regards +-+ | Martin W. Kuria (Mr.) [EMAIL PROTECTED] ++ From: Oleg Bartunov <[EMAIL PROTECTED]> To: Martin Kuria <[EMAIL PROTECTED]> CC: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: [SQL] search facilities Date: Sat, 8 Nov 2003 10:49:41 +0300 (MSK) Check contrib/tsearch2 and http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2 for documentation Oleg On Fri, 7 Nov 2003, Martin Kuria wrote: > Hi, > I have a content management system, for my database driven website developed > using php and postgresql but I don't know how to develop a search facility > for it. > > How do I go about it?. > > I have seen database driven website developed using php and postgresql and > they have their built search facilities where can I learn to develop my > customized search facility please do advice. > > Kind Regards > > +-+ > | Martin W. Kuria (Mr.) [EMAIL PROTECTED] > ++ > > _ > Tired of spam? Get advanced junk mail protection with MSN 8. > http://join.msn.com/?page=features/junkmail > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] SUM() & GROUP BY
Hi again I have two tables I would like to query i.e. service table and
division table
SELECT s.pd_geo, COUNT(s.pd_geo) FROM ser s GROUP BY s.pd_geo;
OUTPUT:
pd_geo | count
--
1| 49
2| 39
3|6
4|54
SELECT d.divisions_name, d.divisions_id)
FROM ser s, ser_divisions d
WHERE d.divisions_id = s.ser_divisions;
division_name | divisions_id
--
DEC| 6
DEPI | 7
DRC|8
How can I create a query that displays How the divisions answered the
question please do assist.
Regrards
Martin W. Kuria
>From: Oliver Elphick <[EMAIL PROTECTED]>
>To: "Muhyiddin A.M Hayat" <[EMAIL PROTECTED]>
>CC: [EMAIL PROTECTED]
>Subject: Re: [SQL] SUM() & GROUP BY
>Date: Sun, 28 Sep 2003 20:56:56 +0100
>
>On Sun, 2003-09-28 at 19:01, Muhyiddin A.M Hayat wrote:
> >
> > hotel=# SELECT
> > hotel-# "public".billing.id,
> > hotel-# "public".billing.guest_id,
> > hotel-# "public".billing.trx_date,
> > hotel-# "public".billing.trx_time,
> > hotel-# "public".billing.payment_method,
> > hotel-# "public".billing.tax,
> > hotel-# "public".billing.dep_id,
> > hotel-# "public".department."name",
> > hotel-# SUM("public".items.price) AS total,
> > hotel-# "public".billing.amount_paid
> > hotel-# FROM
> > hotel-# "public".billing_items
> > hotel-# INNER JOIN "public".billing ON
> > ("public".billing_items.billing_id =
"public".billing.id)
> > hotel-# INNER JOIN "public".department ON
("public".billing.dep_id =
> > "public".department.id)
> > hotel-# INNER JOIN "public".items ON
> > ("public".billing_items.items_id =
"public".items.id)
> > hotel-# GROUP BY "public".billing.id;
> > ERROR: Attribute billing.guest_id must be GROUPed or used in an
> > aggregate function
> > hotel=#
> >
> > What Worng ??
>
>Any items in the select list need to be aggregated (e.g.
>SUM("public".items.price)) or mentioned in the GROUP BY list.
Suppose
>there are several billing.guest_id values for each billing.id; which
>value should be listed in the output?
>
>
>--
>Oliver Elphick[EMAIL PROTECTED]
>Isle of Wight, UK
http://www.lfix.co.uk/oliver
>GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
>
> "Blessed is the man that endureth temptation; for when
> he is tried, he shall receive the crown of life, which
> the Lord hath promised to them that love him."
> James 1:12
>
>
>---(end of broadcast)---
>TIP 8: explain analyze is your friend
_
Help STOP SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] SUM() & GROUP BY
Thanks Huxton, Sorry for not explaining fully here is what I would like to achieve: When I do: SELECT s.pd_geo, COUNT(s.pd_geo) FROM ser s GROUP BY s.pd_geo; OUTPUT: pd_geo | count -- 1| 49 2| 39 3|6 4|54 It outputs the number of entries the Divisions have made Here is what Division table contains: SELECT * FROM ser_divisions; divisions_name | divisions_id --- DEE | 3131 DEPI | 3133 DED | 3134 GBH | 3136 Now I would like to get to know how each Division answered i.e. SELECT s.pd_geo, COUNT(s.pd_geo) FROM ser s WHERE s.ser_divisions = '3131' GROUP BY s.pd_geo; output: pd_geo | count -- 1 | 9 2 | 2 3 | 6 4 | 5 But this is the output I intend to get: divisions_name | pd_geo | count --- DEE | 1 | 9 DEE | 2 | 2 DEE | 3 | 6 DEE | 4 | 5 How do I achieve the above results please do advice thanks again. Kind Regards +-+ | Martin W. Kuria (Mr.) [EMAIL PROTECTED] ++ >From: Richard Huxton <[EMAIL PROTECTED]> >To: Martin Kuria <[EMAIL PROTECTED]> >CC: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] >Subject: Re: [SQL] SUM() & GROUP BY >Date: Fri, 07 May 2004 09:00:43 +0100 > >Martin Kuria wrote: >>Hi again I have two tables I would like to query i.e. service table >>and division table >> >>SELECT s.pd_geo, COUNT(s.pd_geo) FROM ser s GROUP BY s.pd_geo; >>OUTPUT: >>pd_geo | count >>-- >> 1| 49 >> 2| 39 >> 3|6 >> 4|54 >> >>SELECT d.divisions_name, d.divisions_id) >>FROM ser s, ser_divisions d >>WHERE d.divisions_id = s.ser_divisions; >> >>division_name | divisions_id >>-- >> DEC| 6 >> DEPI | 7 >> DRC|8 >> >>How can I create a query that displays How the divisions answered >>the question please do assist. > >Martin - you'll need to explain exactly what you want. Can you show >what outputs you would like given the above data? > >-- > Richard Huxton > Archonet Ltd _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
