Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4057
  • 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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