Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Posts
    77

    Unanswered: SQL (Return the number of row)

    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)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    select count(*) from a;

  3. #3
    Join Date
    Oct 2004
    Location
    Dublin, Ireland
    Posts
    16
    Is it possible to return all the data and the number of rows in one select? Something like select *, count(*) from a?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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?

  5. #5
    Join Date
    Oct 2004
    Location
    Dublin, Ireland
    Posts
    16
    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.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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.

  7. #7
    Join Date
    Oct 2004
    Location
    Dublin, Ireland
    Posts
    16
    I found an easier solution:
    compute count of dname on report;
    select * from dept;

    I know it does not belong in this forum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •