[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4089
  • Last Modified:

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??
0
merimaheen
Asked:
merimaheen
7 Solutions
 
annamalai77Commented:
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
 
ishandoCommented:
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
 
catchmeifuwantCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
grim_toasterCommented:
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
 
konektorCommented:
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
 
JankovskyCommented:
select (select count(1) from emp) as emp,(select count(1) from dept) as dept from dual;
0
 
mjzalewskiCommented:
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now