[Info-Ingres] How to get number of returned rows of another select query?

[tianfeichen at gmail.com]

On May 20, 11:35 am, tianfeic... at gmail.com wrote:
> Hi Roy
>
> Thanks for your query but it gives me different result.
>
> My intention is to get how many airlines in the routing table. So i
> write
>   SELECT count(*)
>   FROM ( SELECT "rt_airline" FROM "route" group by "rt_airline" having
> count("rt_airline") > 1  ) as row_count
>
> The result is 42. It means there are 42 airlines are involved. However
> your query returns 6160. I tried a few other queries based on yours
> but all failed to work.
>
> The most close query I can get so far is:
>   SELECT "rt_airline" FROM "route" group by "rt_airline" having
> count("rt_airline") > 1
>
> It gives me 42 rows such as AAl, ACA, AFL... which are airline code. I
> can then get number of rows in my program. It is acceptable in this
> demodb but for large database there will be much much more records
> returned. Fetching all of them for counting number of returned records
> would be inefficient.
>
> Thanks


Yeah! I finally found the solution:

  select count (distinct (r1.rt_airline))
  from route r1
  where r1.rt_airline in
	(select distinct r2.rt_airline
	 from route r2
	 where r2.rt_airline = r1.rt_airline)

It gives me exactly what I want. Using sub query in where clause is
fairly common way. Do you think it will work with previous version of
Ingres?

BTW, I used and relied too much on ORM and now should reinforce my SQL
skills.

Cheers