pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Group by within table joins
3+ messages / 3 participants
[nested] [flat]

* Group by within table joins
@ 2000-09-13 22:54  Bernie Huang <bernie.huang@ec.gc.ca>
  0 siblings, 2 replies; 3+ messages in thread

From: Bernie Huang @ 2000-09-13 22:54 UTC (permalink / raw)
  To: pgsql-sql

Hi, I have the following SQL:

SELECT ltb.v_id,
       count(ltb.v_id) AS num_of_times_borrowed,
       vtb.equip_attr[1] AS year,
       vtb.equip_attr[3] AS model,
       vtb.equip_attr[4] AS type
FROM log_tb ltb, vehicle_tb vtb
WHERE ltb.v_id=vtb.equip_id
GROUP BY ltb.v_id
ORDER BY year;

"ERROR: Attribute vtb.equip_attr must be GROUPed or used in an aggregate
function"

but, it didn't work.  I want to know how many time each vehicle has been
borrowed. Please help.  Thanks.


- Bernie

Attachments:

bernie.huang.vcf (text/x-vcard, 314B) download begin:vcard n:Huang;Bernie tel;fax:(604)664-9195 tel;work:(604)664-9172 x-mozilla-html:TRUE org:Environment Canada;Standards and Technology Services adr:;;700-1200 West 73 Ave.;Vancouver;BC;V6P 6H9;Canada version:2.1 email;internet:bernie.huang@ec.gc.ca title:Programmer x-mozilla-cpt:;0 fn:Bernie Huang end:vcard
^ permalink raw reply [nested|flat] 3+ messages in thread

* Re: Group by within table joins
@ 2000-09-13 23:19  Josh Berkus <josh@agliodbs.com>
  parent: Bernie Huang <bernie.huang@ec.gc.ca>
  1 sibling, 0 replies; 3+ messages in thread

From: Josh Berkus @ 2000-09-13 23:19 UTC (permalink / raw)
  To: pgsql-sql

Mr. Huang,

Seems to me that your GROUP BY line should read:

GROUP BY ltb.v_id, vtb.equip_attr[1], vtb.equip_attr[3],
vtb.equip_attr[4]

Or am I missing the point?

				-Josh

> SELECT ltb.v_id,
>        count(ltb.v_id) AS num_of_times_borrowed,
>        vtb.equip_attr[1] AS year,
>        vtb.equip_attr[3] AS model,
>        vtb.equip_attr[4] AS type
> FROM log_tb ltb, vehicle_tb vtb
> WHERE ltb.v_id=vtb.equip_id
> GROUP BY ltb.v_id
> ORDER BY year;


-- 
______AGLIO DATABASE SOLUTIONS___________________________
                                        Josh Berkus
   Complete information technology      josh@agliodbs.com
    and data management solutions       (415) 436-9166
   for law firms, small businesses       fax  436-0137
    and non-profit organizations.       pager 338-4078
                        		San Francisco



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Group by within table joins
@ 2000-09-13 23:28  Jie Liang <jliang@ipinc.com>
  parent: Bernie Huang <bernie.huang@ec.gc.ca>
  1 sibling, 0 replies; 3+ messages in thread

From: Jie Liang @ 2000-09-13 23:28 UTC (permalink / raw)
  To: Bernie Huang <bernie.huang@ec.gc.ca>; +Cc: pgsql-sql

Hey,

If you use group then except aggreate functions(count,sum..)
other items in your select list should be in your group list also.

Bernie Huang wrote:

> Hi, I have the following SQL:
>
> SELECT ltb.v_id,
>        count(ltb.v_id) AS num_of_times_borrowed,
>        vtb.equip_attr[1] AS year,
>        vtb.equip_attr[3] AS model,
>        vtb.equip_attr[4] AS type
> FROM log_tb ltb, vehicle_tb vtb
> WHERE ltb.v_id=vtb.equip_id
> GROUP BY ltb.v_id
> ORDER BY year;
>
> "ERROR: Attribute vtb.equip_attr must be GROUPed or used in an aggregate
> function"
>
> but, it didn't work.  I want to know how many time each vehicle has been
> borrowed. Please help.  Thanks.
>
> - Bernie

--
Jie LIANG

Internet Products Inc.

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

jliang@ipinc.com
www.ipinc.com






^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2000-09-13 23:28 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-09-13 22:54 Group by within table joins Bernie Huang <bernie.huang@ec.gc.ca>
2000-09-13 23:19 ` Josh Berkus <josh@agliodbs.com>
2000-09-13 23:28 ` Jie Liang <jliang@ipinc.com>

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