OraFAQ Forum: SQL & PL/SQL » total number of rows from a result set

Home » SQL & PL/SQL » SQL & PL/SQL » total number of rows from a result set

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

total number of rows from a result set [message #20075]

Mon, 29 April 2002 06:18

JZ
Messages: 8
Registered: March 2002

Junior Member

Oracle 8i
How can I do this:
before or after I run a query, is there a way to know the total number of rows in the result set?

For example:

query like: select student_name from students where sex='male';

Can I know how many male students are there? (The real query could be more complicated than the example)

Thnx!

Report message to a moderator

Re: total number of rows from a result set [message #20077 is a reply to message #20075]

Mon, 29 April 2002 06:21

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

USE COUNT.
--------------------------------------------------
1* select ename from emp where job='CLERK'
SQL> /

ENAME
----------
SMITH
ADAMS
JAMES
MILLER

1* select count(ename) from emp where job='CLERK'
SQL> /

COUNT(ENAME)
------------
4

Report message to a moderator

my example is too simple... [message #20078 is a reply to message #20075]

Mon, 29 April 2002 06:24

JZ
Messages: 8
Registered: March 2002

Junior Member

How can I count the # of rows being returned from the following query:

select n.destination,n.nfseverity,SUM(n.eventcount)
from highseverityevents n,devices i , nfalarmctgmappings m
where n.deviceid in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19)
and n.destination IS NOT null
and n.deviceid = i.deviceid
and n.nfalarmid=m.nfalarmid
and m.nfctg like '%'
and n.apptimestamp>= to_date('04/20/2002 00:00:00')
and n.apptimestamp< to_date('04/20/2002 01:00:00')
group by n.destination, n.nfseverity
UNION
select n.destination,n.nfseverity,SUM(n.eventcount)
from lowseverityevents n,devices i , nfalarmctgmappings m
where n.deviceid in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19)
and n.destination IS NOT null
and n.deviceid = i.deviceid
and n.nfalarmid=m.nfalarmid
and m.nfctg like '%'
and n.apptimestamp>= to_date('04/20/2002 00:00:00')
and n.apptimestamp< to_date('04/20/2002 01:00:00')
and 0= 0
group by n.destination,n.nfseverity
order by 3 desc;

Report message to a moderator

Re: my example is too simple... [message #20079 is a reply to message #20075]

Mon, 29 April 2002 06:39

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

use
select rownum, ..... your select statemment,,,from...;
this will not return the count anyhow.
else
use a plsql cursor select statement and make use of %rowcount attribute.

Mahesh Rajendran

Report message to a moderator