SQL (Return the number of row)

gop373 · #1 (**permalink**) 10-18-04, 04:48

Is it possible to write sql to return the number of row in the table ? If so how ?

For example , table A
id name
21 AA
33 BB
11 CC

From this example , it should return 3 (because 3 rows)

andrewst · #2 (**permalink**) 10-18-04, 05:46

select count(*) from a;

kevin30k · #3 (**permalink**) 10-18-04, 07:01

Is it possible to return all the data and the number of rows in one select? Something like select *, count(*) from a?

andrewst · #4 (**permalink**) 10-18-04, 07:16

Yes, if you really must:

Code:

  1  select dept.*, (select count(*) from dept) cnt from dept;

    DEPTNO DNAME          LOC                  CNT
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK               4
        20 RESEARCH       DALLAS                 4
        30 SALES          CHICAGO                4
        40 OPERATIONS     BOSTON                 4

Is that what you meant?

kevin30k · #5 (**permalink**) 10-18-04, 07:21

Hmmm. That works but it looks inefficient. I just need to present a total at the end so a separate select at the end would be better. I am selecting over 2000 rows.

Another possibility might be to use a cursor and count the number of rows returned.

I'm just trying to avoid a second select statement.

andrewst · #6 (**permalink**) 10-18-04, 07:32

Yes, it is inefficient. I was trying to give a "pure SQL" solution as this is a "pure SQL" forum. If you want to display a total number of selected records at the end, then probably your DBMS already has a built-in way to do this. For example, in Oracle SQL Plus:

Code:

SQL>  select * from dept;

    DEPTNO DNAME          LOC                  MGR
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS              7782
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.

And in Oracle PL/SQL:

Code:

SQL> declare
  2    cursor c is select * from dept;
  3    r c%ROWTYPE;
  4  begin
  5    open c;
  6    loop
  7      fetch c into r;
  8      exit when c%notfound;
  9    end loop;
 10    dbms_output.put_line('Number of rows='||c%rowcount);
 11    close c;
 12  end;
 13  /
Number of rows=4

PL/SQL procedure successfully completed.

kevin30k · #7 (**permalink**) 10-18-04, 10:07

I found an easier solution:
compute count of dname on report;
select * from dept;

I know it does not belong in this forum.

Internet Services

Web Development

Digital Marketing

Consumer Tech