pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGroup 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>
2000-09-13 23:19 ` Re: Group by within table joins Josh Berkus <josh@agliodbs.com>
2000-09-13 23:28 ` Re: Group by within table joins Jie Liang <jliang@ipinc.com>
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 22:54 Group by within table joins Bernie Huang <bernie.huang@ec.gc.ca>
@ 2000-09-13 23:19 ` Josh Berkus <josh@agliodbs.com>
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 22:54 Group by within table joins Bernie Huang <bernie.huang@ec.gc.ca>
@ 2000-09-13 23:28 ` Jie Liang <jliang@ipinc.com>
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