How to get number of returned rows of another select query?

To get number of returned rows directly in sql, I usually write sql
this way:
SELECT count(*) as row_count
FROM ( SELECT DISTINCT "rt_airline" FROM "route" WHERE "rt_airline"
IS NOT NULL )

It works in Access, SQLServer, Oracle, but get syntax error in Ingres.

Is there any way to directly get the result count of sub SELECT query?
Or is there any special function/keywords to do this?

Thanks.

On May 18, 1:40 pm, tianfeic...@gmail.com wrote:
> To get number of returned rows directly in sql, I usually write sql
> this way:
> SELECT count(*) as row_count
> FROM ( SELECT DISTINCT "rt_airline" FROM "route" WHERE "rt_airline"
> IS NOT NULL )
>
> It works in Access, SQLServer, Oracle, but get syntax error in Ingres.
>
> Is there any way to directly get the result count of sub SELECT query?
> Or is there any special function/keywords to do this?
>
> Thanks.

Fix it by putting "as" behind the sub query. Like this:

SELECT count(*)
FROM ( SELECT DISTINCT "rt_airline" FROM "route" WHERE "rt_airline"
IS NOT NULL ) as row_count

Get it by searching PostgreSQL document...

However this modified sql will not run in Oracle. That is why I don't
like writing sql...

Cheers

Hi tianfeichen,

That will not work in most versions of Ingres. I suspect you are using
the absolute latest Ingres2006 release 2. Is that so? I ask coz I tried
that here on my Ingres2006 site and it didn't work.

Martin Bowes
> On May 18, 1:40 pm, tianfeic...@gmail.com wrote:
> > To get number of returned rows directly in sql, I usually write sql
> > this way:
> > SELECT count(*) as row_count
> > FROM ( SELECT DISTINCT "rt_airline" FROM "route" WHERE
> > "rt_airline"
> > IS NOT NULL )
> >
> > It works in Access, SQLServer, Oracle, but get syntax error in
> > Ingres.
> >
> > Is there any way to directly get the result count of sub SELECT
> > query? Or is there any special function/keywords to do this?
> >
> > Thanks.
>
> Fix it by putting "as" behind the sub query. Like this:
>
> SELECT count(*)
> FROM ( SELECT DISTINCT "rt_airline" FROM "route" WHERE "rt_airline"
> IS NOT NULL ) as row_count
>
> Get it by searching PostgreSQL document...
>
> However this modified sql will not run in Oracle. That is why I don't
> like writing sql...
>
> Cheers
>
> _______________________________________________
> Info-Ingres mailing list
> Info-Ingres-at-kettleriverconsulting.com
> http://www.kettleriverconsulting.com...fo/info-ingres
>

Works in 2006 release 2:

apj-sol1..~ i2006r2 > sql jrd
INGRES TERMINAL MONITOR Copyright 2007 Ingres Corporation
Ingres 2006 Release 2 SPARC SOLARIS Version II 9.1.0 (su9.us5/123)
login
Fri May 18 18:08:31 2007

continue
* select count(*) from ( select distinct c1 from t1) as row_count;
* \g
Executing . . .


┌─────────────�
│col1 │
├─────────────┤
│ 2│
└─────────────┘
(1 row)

John


On May 18, 5:43 pm, martin.bo...@ctsu.ox.ac.uk wrote:
> Hi tianfeichen,
>
> That will not work in most versions of Ingres. I suspect you are using
> the absolute latest Ingres2006 release 2. Is that so? I ask coz I tried
> that here on my Ingres2006 site and it didn't work.
>
> Martin Bowes
>
> > On May 18, 1:40 pm, tianfeic...@gmail.com wrote:
> > > To get number of returned rows directly in sql, I usually write sql
> > > this way:
> > > SELECT count(*) as row_count
> > > FROM ( SELECT DISTINCT "rt_airline" FROM "route" WHERE
> > > "rt_airline"
> > > IS NOT NULL )
>
> > > It works in Access, SQLServer, Oracle, but get syntax error in
> > > Ingres.
>
> > > Is there any way to directly get the result count of sub SELECT
> > > query? Or is there any special function/keywords to do this?
>
> > > Thanks.
>
> > Fix it by putting "as" behind the sub query. Like this:
>
> > SELECT count(*)
> > FROM ( SELECT DISTINCT "rt_airline" FROM "route" WHERE "rt_airline"
> > IS NOT NULL ) as row_count
>
> > Get it by searching PostgreSQL document...
>
> > However this modified sql will not run in Oracle. That is why I don't
> > like writing sql...
>
> > Cheers
>
> > _______________________________________________
> > Info-Ingres mailing list
> > Info-Ing...@kettleriverconsulting.com
> >http://www.kettleriverconsulting.com...fo/info-ingres

wrote in message
news:1179463228.542379.292110-at-e65g2000hsc.googlegr oups.com...
> To get number of returned rows directly in sql, I usually write sql
> this way:
> SELECT count(*) as row_count
> FROM ( SELECT DISTINCT "rt_airline" FROM "route" WHERE "rt_airline"
> IS NOT NULL )
>
> It works in Access, SQLServer, Oracle, but get syntax error in Ingres.
>
> Is there any way to directly get the result count of sub SELECT query?
> Or is there any special function/keywords to do this?

The ability to use a subquery in the FROM clause was introduced in Ingres
2006 Release 2, so this won't work with any version of Ingres earlier than
that.

Secondly--and I don't know if this is ANSI/ISO standard or not--Ingres
insists that the subquery in the FROM clause has to have a correlation name,
so if you take the SQL you wrote above and just add an x (say) after the
closing bracket then it will work correctly. Otherwise you get a misleading
syntax error message, probably about an unexpected EOF.

As an aside, this query can be written more portably as follows:

SELECT count(DISTINCT "rt_airline") FROM "route";

This works equivalently to your query because unlike count(*), count(column)
ignores nulls.

Roy

Ingres Users Association Spring Conference, 6 June 2007, London
Secure your place by registering NOW via the IUA web site
http://www.iua.org.uk/

"John Dennis" wrote in message
news:1179475707.837009.209350-at-u30g2000hsc.googlegr oups.com...
> Works in 2006 release 2:
>
> apj-sol1..~ i2006r2 > sql jrd
> INGRES TERMINAL MONITOR Copyright 2007 Ingres Corporation
> Ingres 2006 Release 2 SPARC SOLARIS Version II 9.1.0 (su9.us5/123)
> login
> Fri May 18 18:08:31 2007
>
> continue
> * select count(*) from ( select distinct c1 from t1) as row_count;
> * \g
> Executing . . .
>
>
> +-------------+
> ¦col1 ¦
> +-------------¦
> ¦ 2¦
> +-------------+
> (1 row)

A couple of people have suggested adding "...as row_count" or similar to the
end of the query to make it work. It makes the error message go away, but
not for the reason being suggested. If you look at the output from this
example you will see that the "...as row_count" is being ignored; the result
column is being called "col1".

What's happened here is that the word "as" seems to have been taken as the
correlation name for the result table. Goodness knows why "row_count"
wasn't then flagged as a syntax error though.

Roy

Ingres Users Association Spring Conference, 6 June 2007, London
Secure your place by registering NOW via the IUA web site
http://www.iua.org.uk/

The 'as row_count' simply specifies the alias of the derived table.
You can specify the column name of the derived table too in the alias:

SELECT * FROM (SELECT DISTINCT c1 FROM t1) AS row_count(row_count);
will specify row_count as the column name of the derived table. The
column name of the result set will be row_count

SELECT count(*) AS row_count FROM ( select distinct c1 from t1) AS
row_count(nr);
and this example specifies row_count as the column name of the result
set.

and there's no need to use AS. Just like specifying a table alias, its
optional.

But in this case, SELECT COUNT(DISTINCT (...)) will be neater and will
work in Ingres 2006 release 1 and earlier.

On May 18, 6:44 pm, "Roy Hann"
wrote:
> "John Dennis" wrote in message
>
> news:1179475707.837009.209350-at-u30g2000hsc.googlegr oups.com...
>
>
>
> > Works in 2006 release 2:
>
> > apj-sol1..~ i2006r2 > sql jrd
> > INGRES TERMINAL MONITOR Copyright 2007 Ingres Corporation
> > Ingres 2006 Release 2 SPARC SOLARIS Version II 9.1.0 (su9.us5/123)
> > login
> > Fri May 18 18:08:31 2007
>
> > continue
> > * select count(*) from ( select distinct c1 from t1) as row_count;
> > * \g
> > Executing . . .
>
> > +-------------+
> > ¦col1 ¦
> > +-------------¦
> > ¦ 2¦
> > +-------------+
> > (1 row)
>
> A couple of people have suggested adding "...as row_count" or similar to the
> end of the query to make it work. It makes the error message go away, but
> not for the reason being suggested. If you look at the output from this
> example you will see that the "...as row_count" is being ignored; the result
> column is being called "col1".
>
> What's happened here is that the word "as" seems to have been taken as the
> correlation name for the result table. Goodness knows why "row_count"
> wasn't then flagged as a syntax error though.
>
> Roy
>
> Ingres Users Association Spring Conference, 6 June 2007, London
> Secure your place by registering NOW via the IUA web sitehttp://www.iua.org.uk/

On May 18, 5:37 pm, "Roy Hann"
wrote:
> wrote in message
>
> news:1179463228.542379.292110-at-e65g2000hsc.googlegr oups.com...
>
> > To get number of returned rows directly in sql, I usually write sql
> > this way:
> > SELECT count(*) as row_count
> > FROM ( SELECT DISTINCT "rt_airline" FROM "route" WHERE "rt_airline"
> > IS NOT NULL )
>
> > It works in Access, SQLServer, Oracle, but get syntax error in Ingres.
>
> > Is there any way to directly get the result count of sub SELECT query?
> > Or is there any special function/keywords to do this?
>
> The ability to use a subquery in the FROM clause was introduced in Ingres
> 2006 Release 2, so this won't work with any version of Ingres earlier than
> that.
>
> Secondly--and I don't know if this is ANSI/ISO standard or not--Ingres
> insists that the subquery in the FROM clause has to have a correlation name,
> so if you take the SQL you wrote above and just add an x (say) after the
> closing bracket then it will work correctly. Otherwise you get a misleading
> syntax error message, probably about an unexpected EOF.
>
> As an aside, this query can be written more portably as follows:
>
> SELECT count(DISTINCT "rt_airline") FROM "route";
>
> This works equivalently to your query because unlike count(*), count(column)
> ignores nulls.
>
> Roy
>
> Ingres Users Association Spring Conference, 6 June 2007, London
> Secure your place by registering NOW via the IUA web sitehttp://www.iua.org.uk/

Hi Roy and Martin

You are correct. I'm using latest Ingres2006 release 2. Thanks for
letting me know that it won't work on previous versions of Ingres. And
Roy thank you again for the simpilifed query. It works well.

However I have another similar query gives me differnet result. For
example, to get how many value patterns out there I write:
SELECT count(*)
FROM ( SELECT "rt_airline" FROM "route" group by "rt_airline" having
count("rt_airline") > 1 ) as row_count
It returns a number (a single value 42) for me.

Due to it will not work in previous versions, I rewrite it to
SELECT count("rt_airline") FROM "route" group by "rt_airline" having
count("rt_airline") > 1
This time it returns me a list of numbers (42 rows), which are not
even correct value patterns.

SELECT "rt_airline" FROM "route" group by "rt_airline" having
count("rt_airline") > 1
will give me correct patterns.

Because there may be large amount of patterns in real database,
getting all patterns as my last query is not desired. Do you have any
idea how to tune it to work in previous version?

Thanks

wrote in message
news:1179547113.648215.251890-at-h2g2000hsg.googlegro ups.com...
> On May 18, 5:37 pm, "Roy Hann"
> wrote:
>
> However I have another similar query gives me differnet result. For
> example, to get how many value patterns out there I write:
> SELECT count(*)
> FROM ( SELECT "rt_airline" FROM "route" group by "rt_airline" having
> count("rt_airline") > 1 ) as row_count
> It returns a number (a single value 42) for me.
>
> Due to it will not work in previous versions, I rewrite it to
> SELECT count("rt_airline") FROM "route" group by "rt_airline" having
> count("rt_airline") > 1
> This time it returns me a list of numbers (42 rows), which are not
> even correct value patterns.

This query is not equivalent to the one you are trying to replace.

> SELECT "rt_airline" FROM "route" group by "rt_airline" having
> count("rt_airline") > 1
> will give me correct patterns.
>
> Because there may be large amount of patterns in real database,
> getting all patterns as my last query is not desired. Do you have any
> idea how to tune it to work in previous version?

This query will do what your first query above is intended to do, and is
portable to all versions of Ingres:

select count(r1.rt_airline)
from route r1
where 1 < (select count(r2.rt_airline)
from route r2
where r2.rt_airline = r1.rt_airline)

I admit it looks weird. It would be easier to understand if the scalar
subquery could appear on the left of a test for > 1 like you had in your
query, but unfortunately that is allowed only since Ingres 2006.

Roy

Ingres Users Association Spring Conference, 6 June 2007, London
Secure your place by registering NOW via the IUA web site
http://www.iua.org.uk/

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