Home » SQL & PL/SQL » SQL & PL/SQL » Total Count
Total Count [message #289229] Thu, 20 December 2007 06:22 Go to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Hi Guys,

We have to get the overall count after sub-dividing the table based on JOB and Gender.
like:
select count(*) "NO OF JobsPerDept",JOB,DEPTNO from emp
group by job,deptno

  No OF JobsPerDept   JOB           Deptno         TOTAL COUNT
 -----------------   -----------  ------------     ----------
      2                CLERK         20               8
      1                CLERK         30
      2                ANALYST       20
      1                MANAGER       10
      2                MANAGER       20


I am facing difficulty to get the final "TOTAL COUNT".
Any help is highly appreciated.

Regards
Srini...
Re: Total Count [message #289236 is a reply to message #289229] Thu, 20 December 2007 07:07 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
You requirements are a bit unclear.

Are you looking for the count at the job level, dept/job level or a grand total count or all of the above ?
Re: Total Count [message #289240 is a reply to message #289236] Thu, 20 December 2007 07:18 Go to previous messageGo to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Hi Marcl,

I want the grand total count i.e., the total number of employees in emp table.


Regards
Srini...
Re: Total Count [message #289241 is a reply to message #289229] Thu, 20 December 2007 07:19 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Would something like this do the job?
SQL> break on report
SQL> compute sum of cnt on report
SQL>
SQL> select deptno, count(*) cnt
  2  from emp
  3  group by deptno
  4  order by deptno;

    DEPTNO        CNT
---------- ----------
        10          3
        20          5
        30          6
           ----------
sum                14

SQL>
Re: Total Count [message #289251 is a reply to message #289240] Thu, 20 December 2007 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select deptno, count(*) cnt
  2  from emp
  3  group by rollup(deptno)
  4  order by grouping(deptno), deptno;
    DEPTNO        CNT
---------- ----------
        10          3
        20          5
        30          6
                   14

4 rows selected.

Regards
Michel

[Updated on: Fri, 21 December 2007 00:24]

Report message to a moderator

Re: Total Count [message #289253 is a reply to message #289251] Thu, 20 December 2007 07:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ok, and the obvious, in case you need the total in the same row:
SQL> select count(*) "NO OF JobsPerDept"
  2  ,      job
  3  ,      deptno
  4  ,      count(*) over () "Total number of emps"
  5  from   emp
  6  group  by job
  7  ,      deptno
  8  /

NO OF JobsPerDept JOB           DEPTNO Total number of emps
----------------- --------- ---------- --------------------
                2 ANALYST           20                    9
                1 CLERK             10                    9
                2 CLERK             20                    9
                1 CLERK             30                    9
                1 MANAGER           10                    9
                1 MANAGER           20                    9
                1 MANAGER           30                    9
                1 PRESIDENT         10                    9
                4 SALESMAN          30                    9

9 rows selected.
Re: Total Count [message #289344 is a reply to message #289253] Thu, 20 December 2007 19:41 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
And of course:
SQL> break on "Total number of emps"
SQL>
SQL> select count(*) "NO OF JobsPerDept"
  2   , job
  3   , deptno
  4   , count(*) over () "Total number of emps"
  5   from      emp
  6   group     by job
  7   , deptno
  8  /

NO OF JobsPerDept JOB           DEPTNO Total number of emps
----------------- --------- ---------- --------------------
                2 ANALYST           20                    9
                1 CLERK             10
                2 CLERK             20
                1 CLERK             30
                1 MANAGER           10
                1 MANAGER           20
                1 MANAGER           30
                1 PRESIDENT         10
                4 SALESMAN          30

9 rows selected.


Ross Leishman
Re: Total Count [message #289364 is a reply to message #289344] Thu, 20 December 2007 23:45 Go to previous messageGo to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Hi All,

Thanks a lot for your timely response.

rleishman,

The total count of the employees should be 14,but from your query i can see only 9(Total number of emps).My requirement is i want the total count of the employees from emp table to be 14.
Kindly post the query that will solve my problem.

Thanks again,
srini...

[Updated on: Thu, 20 December 2007 23:52]

Report message to a moderator

Re: Total Count [message #289370 is a reply to message #289364] Fri, 21 December 2007 00:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry, I copied too much of your original query and did not really check the results.

SQL> select deptno
  2  ,      count(distinct job) "Distinct jobs per dept"
  3  ,      total               "Total emps"
  4  from   (select deptno
  5          ,      job
  6          ,      count(*) over () total
  7          from   emp
  8          group by deptno, job, empno
  9         )
 10  group by deptno
 11  ,     total
 12  order by deptno;

    DEPTNO Distinct jobs per dept Total emps
---------- ---------------------- ----------
        10                      3         14
        20                      3         14
        30                      3         14
Re: Total Count [message #289371 is a reply to message #289251] Fri, 21 December 2007 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Anothe one:
SQL> select deptno, 
  2         count(*) cnt,
  3         decode(row_number () over (order by deptno),1,count(*) over ()) total
  4  from emp
  5  group by deptno
  6  order by deptno;
    DEPTNO        CNT      TOTAL
---------- ---------- ----------
        10          3          3
        20          5
        30          6

3 rows selected.

Regards
Michel
Re: Total Count [message #289376 is a reply to message #289229] Fri, 21 December 2007 00:47 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

HI Srinivas,

You had given so many examples. Now you can get your requirement. If want one more example then take this one also

SELECT * FROM emp 
ORDER BY depno;

ENAME	ENO	DEPNO	SAL

aithal	2	30	3000
prem	7	30	4000
kumar	5	30	5000
kiran	1	40	1000
vishal	26	40	3500
hai	4	55	1500
prathwi	3	70	5000
prathwi	10	70	2000
bharath	14	75	6000


SELECT   DEPNO,
         COUNT (*) CNT
    FROM EMP
GROUP BY GROUPING SETS (DEPNO, ())

DEPNO	CNT

30	3
40	2
55	1
70	2
75	1
	9


Again if you want count with employee break up then check this:

SELECT   DEPNO,
         ENAME,
         COUNT (*) CNT,
		 GROUPING(ename) 
    FROM EMP
GROUP BY GROUPING SETS ((DEPNO, ENAME), DEPNO, ())
ORDER BY depno;

DEPNO	ENAME	CNT	GROUPING(ENAME)

30	aithal	1	0
30	kumar	1	0
30	prem	1	0
30		3	1
40	kiran	1	0
40	vishal	1	0
40		2	1
55	hai	1	0
55		1	1
70	prathwi	2	0
70		2	1
75	bharath	1	0
75		1	1
		9	1
Re: Total Count [message #289382 is a reply to message #289229] Fri, 21 December 2007 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select distinct 
  2         deptno "Deptno", job "Job", 
  3         count(*) over (partition by deptno, job) "EmpInJobPerDept",
  4         count(distinct job) over (partition by deptno) "JobsPerDept",
  5         decode(row_number() over (order by deptno,job),1,count(*) over ())
  6           "TotalEmp"
  7  from emp
  8  order by deptno, job
  9  /
    Deptno Job       EmpInJobPerDept JobsPerDept   TotalEmp
---------- --------- --------------- ----------- ----------
        10 CLERK                   1           3         14
        10 MANAGER                 1           3
        10 PRESIDENT               1           3
        20 ANALYST                 2           3
        20 CLERK                   2           3
        20 MANAGER                 1           3
        30 CLERK                   1           3
        30 MANAGER                 1           3
        30 SALESMAN                4           3

9 rows selected.

Regards
Michel
Re: Total Count [message #289399 is a reply to message #289382] Fri, 21 December 2007 04:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Nice wrap up of all possible ways the original poster might want it, Michel!
... or would he also want grouped rollups over all these count? Wink
Re: Total Count [message #289400 is a reply to message #289229] Fri, 21 December 2007 04:28 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

These articles are simple and easy to understand.

Enhanced Aggregation, Cube, Grouping and Rollup

Analytic functions

Regards,
Kiran.
Re: Total Count [message #289552 is a reply to message #289382] Mon, 24 December 2007 00:05 Go to previous message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Thanks Michel and every one who contributed...it really helped me solve my issue.


Regards
Srini...

[Updated on: Mon, 24 December 2007 00:25]

Report message to a moderator

Previous Topic: Group by clause
Next Topic: join 4 tables
Goto Forum:
  


Current Time: Sun Dec 04 02:51:57 CST 2016

Total time taken to generate the page: 0.09871 seconds