sql high hand please help. - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

sql high hand please help.

sql high hand please help.# Database - 数据库

w*n2001-02-04 08:02

1 楼

say i have three tables, motel, resorts, bed-breakfirst,
and all three tables have a column called price.
and i want to calculate avg, max, and min price
from all three tables. how do i do that?
i know select avg(price) from table-name
but it only gives me avg price from only one table,
how do i get all three tables' avg???

b*e2001-02-04 08:02

2 楼

select avg(price) from
(select price from motel union
select price from resorts union
select price from bed_breakfast);

【在 w******n 的大作中提到】

: say i have three tables, motel, resorts, bed-breakfirst,
: and all three tables have a column called price.
: and i want to calculate avg, max, and min price
: from all three tables. how do i do that?
: i know select avg(price) from table-name
: but it only gives me avg price from only one table,
: how do i get all three tables' avg???

w*n2001-02-04 08:02

3 楼

informix sql says u can't use union inside a sub query.
any other ideas???

【在 b****e 的大作中提到】

: select avg(price) from
: (select price from motel union
: select price from resorts union
: select price from bed_breakfast);

b*e2001-02-04 08:02

4 楼

sorry don't know informix well.. dump informix :)
who still uses it anyway :)

【在 w******n 的大作中提到】

: informix sql says u can't use union inside a sub query.
: any other ideas???

f*c2001-02-04 08:02

5 楼

Use create view that is the union of several tables, and then issue query against
the view?
Don't know whether it works or not in informix. It is ok in MSSql server.