Solved

select count(*),count(*) from emp,dept

Posted on 2004-09-23
8
3,975 Views
Last Modified: 2007-12-19
I want the results like that

emp  dept  
14   4

instead of
select count(*) from emp;
14
select count(*) from dept;
4


I want one sql statement to find the number of rows in all the tables for a user.

can you help me??
0
Comment
Question by:merimaheen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 8

Assisted Solution

by:annamalai77
annamalai77 earned 30 total points
ID: 12139958
hi

try this

select count(distinct a.emp_no), count(distinct b.dept_no)
from emp a, dept b
where a.dept_no(+) = b.dept_no;


regards
annamalai
0
 
LVL 15

Accepted Solution

by:
ishando earned 55 total points
ID: 12140000
You could do:

select t1.emp, t2.dept
from (select count(0) emp from emp) t1,
     (select count(0) dept from dept) t2;

but you need to explicitly write your select count(0) statement for each table in the schema.

the more usual way to do this is in a PL/SQL block - eg

declare
  cursor cTabs is select table_name from user_tables;
  sqlstr varchar2(100);
  rcount integer;
begin
  for c1 in cTabs loop
    sqlstr := 'select count(0) from ' || c1.table_name;
    execute immediate sqlstr into rcount;
    dbms_output.put_line(rpad(c1.table_name, 30, ' ') || rcount);
  end loop;
end;
/
0
 
LVL 12

Assisted Solution

by:catchmeifuwant
catchmeifuwant earned 20 total points
ID: 12140125
select sum(decode(TBL,'E',cnt,0)) as EMP,sum(decode(TBL,'D',cnt,0)) as DEPT
from
(
(select 'E' as TBL,count(*) as cnt from emp)
union all
(select 'D' as TBL,count(*) as cnt from dept)
)
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 7

Assisted Solution

by:grim_toaster
grim_toaster earned 25 total points
ID: 12140384
Two options, one for your very simple example you can use:

SELECT (SELECT COUNT(*) FROM emp) AS emp, (SELECT COUNT(*) FROM dept) AS dept
FROM   dual

Or for EVERY object, in SQL*Plus (or convert to a function and have whatever you want to process the results):

SET AUTOPRINT ON;
VAR x REFCURSOR;
DECLARE
    v_sql_query VARCHAR2(30000);
BEGIN
    v_sql_query := 'SELECT 1 AS dummy';
    FOR rec IN (SELECT table_name FROM user_tables) LOOP
        v_sql_query := v_sql_query || ', (SELECT count(1) FROM ' || rec.table_name || ') AS ' || rec.table_name;
    END LOOP;
      v_sql_query := v_sql_query || ' FROM dual';

      OPEN :x FOR v_sql_query;
END;
0
 
LVL 9

Assisted Solution

by:konektor
konektor earned 20 total points
ID: 12141134
select sum(col1) as col1, sum(col2) as col2
 from
(select 0 as col1, count(*) as col2 from emp
union all
select count(*) as col1, 0 as col2 from dept)
/
0
 
LVL 6

Assisted Solution

by:Jankovsky
Jankovsky earned 20 total points
ID: 12141334
select (select count(1) from emp) as emp,(select count(1) from dept) as dept from dual;
0
 
LVL 3

Assisted Solution

by:mjzalewski
mjzalewski earned 30 total points
ID: 12153417
Use logical tables

SELECT e.emp_count, d.dept_count
  FROM (
    SELECT COUNT(*) emp_count
      FROM Emp
    ) e,
    SELECT COUNT(*) dept_count
      FROM Dept
   ) d

Here I use a SELECT statement where you would expect a table name. So I make two logical tables, each with one row and one column, and each containing the count of rows in its respective real table.

My logical tables are named e (with a single column named e.emp_count), and d (with a single column named d.dept_count)
0
 

Author Comment

by:merimaheen
ID: 12156816
wow so many solutions, i increase the points and split to all.
Thanks
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question