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

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??
merimaheenAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ishandoConnect With a Mentor Commented:
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
 
annamalai77Connect With a Mentor Commented:
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
 
catchmeifuwantConnect With a Mentor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
grim_toasterConnect With a Mentor Commented:
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
 
konektorConnect With a Mentor Commented:
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
 
JankovskyConnect With a Mentor Commented:
select (select count(1) from emp) as emp,(select count(1) from dept) as dept from dual;
0
 
mjzalewskiConnect With a Mentor Commented:
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
 
merimaheenAuthor Commented:
wow so many solutions, i increase the points and split to all.
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.