Thread: Group by within table joins
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
Attachment
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
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 informationtechnology 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