Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-09-23
8
Medium Priority
?
4,024 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 120 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 220 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 80 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 7

Assisted Solution

by:grim_toaster
grim_toaster earned 100 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 80 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 80 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 120 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

721 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