Home » SQL & PL/SQL » SQL & PL/SQL » regarding count (oracle 9i)
regarding count [message #287179] Tue, 11 December 2007 05:59 Go to next message
raghunalumachu
Messages: 12
Registered: November 2007
Location: MUMBAI
Junior Member
Hi i have a table called management with two columns called mngnum, mngname,mngdept

with data as following
----------------------
mngnum mngname mngdept
---------------------------------------------------------------
101 kiran 10
109 suman 20
106 mahesh 20
107 pavan 30
108 nitin 20
111 siddu 20

i would like to retreive all mngnum from one mngdept
like this

mngnum count(mngnum) mngdept
(some columnname)
109 1 20
106 2 20
108 3 20
111 4 20


Thanks in Advance,
Raghu

Re: regarding count [message #287182 is a reply to message #287179] Tue, 11 December 2007 06:12 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Hi,
In future, please format your posts according to the guidelines helpfully posted in the sticjky at the top of the forum list.
As to your problem, you are very close.
Where you say (some columnname) well that would be the column that you want to break the count down by.
For example if I wanted to count the number of people who belonged to a certain group and I had a table structure like:

person    group   Other column
Jane        10       T
Rod         10       T
Freddy      15       F
Bungle      10       T
George      15       F
Zippy       20       F

then My select would look something like

SELECT COUNT(*), group

Obviously I need to add in the FROM clause and there is another clause required when performing actions like count, sum, max etc.
Read up on the GROUP BY clause
here
Please remember to also go and read and follow the Orafaq guide
Re: regarding count [message #287189 is a reply to message #287179] Tue, 11 December 2007 06:26 Go to previous messageGo to next message
raghunalumachu
Messages: 12
Registered: November 2007
Location: MUMBAI
Junior Member
Sorry for the confusion created.I am reposting my message

I have a table called management with two columns called mngnum, mngname,mngdept

with data as following
----------------------
mngnum mngname mngdept
------- ---------------- ------------
101 kiran 10
109 suman 20
106 mahesh 20
107 pavan 30
108 nitin 20
111 siddu 20

i would like to retreive all mngnum from one mngdept
like this

mngnum count(mngnum) mngdept
109 1 20
106 2 20
108 3 20
111 4 20

I would like to have a query which displays mngnum from a partcular mngdpet and along with that a logical column which has value 1 for the first row retrieved value 2 for second row and so on.


Thanks in Advance,
Raghu
Re: regarding count [message #287197 is a reply to message #287189] Tue, 11 December 2007 06:45 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
OK, Before we go any further (and before I even try to answer your question), you are going to have to read and follow the guidelines. I'll even post the link The description of your issue is not clear. Please provide a test case. Thanks.
Re: regarding count [message #287225 is a reply to message #287179] Tue, 11 December 2007 08:27 Go to previous message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

@Raghu,

SQL> CREATE TABLE MANAGEMENT
  2  (
  3    ENAME   VARCHAR2(10 BYTE),
  4    ENO     NUMBER(4),
  5    DEPTNO  NUMBER(4)
  6  );

Table created.

SQL> INSERT INTO MANAGEMENT ( ENAME, ENO, DEPTNO ) VALUES ( 
  2  'Raghu', 13, 20); 

1 row created.

SQL> INSERT INTO MANAGEMENT ( ENAME, ENO, DEPTNO ) VALUES ( 
  2  'Kiran', 11, 101); 

1 row created.

SQL> INSERT INTO MANAGEMENT ( ENAME, ENO, DEPTNO ) VALUES ( 
  2  'pragathi', 15, 20); 

1 row created.

SQL> INSERT INTO MANAGEMENT ( ENAME, ENO, DEPTNO ) VALUES ( 
  2  'yash', 12, 20); 

1 row created.

SQL> INSERT INTO MANAGEMENT ( ENAME, ENO, DEPTNO ) VALUES ( 
  2  'sakshi', 10, 60); 

1 row created.

SQL> INSERT INTO MANAGEMENT ( ENAME, ENO, DEPTNO ) VALUES ( 
  2  'manushya', 8, 80); 

1 row created.

SQL> INSERT INTO MANAGEMENT ( ENAME, ENO, DEPTNO ) VALUES ( 
  2  'nisha', 7, 20); 

1 row created.

SQL> INSERT INTO MANAGEMENT ( ENAME, ENO, DEPTNO ) VALUES ( 
  2  'usha', 6, 60); 

1 row created.

SQL> COMMIT;

Commit complete.


SQL> SELECT eno, deptno, ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY deptno) sequence
  2    FROM MANAGEMENT;

       ENO     DEPTNO   SEQUENCE
---------- ---------- ----------
        13         20          1
        15         20          2
         7         20          3
        12         20          4
        10         60          1
         6         60          2
         8         80          1
        11        101          1

8 rows selected.

SQL> drop table MANAGEMENT;

Table dropped.


You need to work on ROW_NUMBER and PARTITION BY clauses.

And next time, you have to use proper TAGs while posting codes as i did above and you have to provide test cases like create table and insert table scripts. This will help to analysis your data. For more details refere the Orafaq guide. http://www.orafaq.com/forum/t/88153/0/

Best Regards,
Kiran.
Previous Topic: Filter in hierarchical query
Next Topic: Update Join View
Goto Forum:
  


Current Time: Fri Dec 02 19:06:06 CST 2016

Total time taken to generate the page: 0.20016 seconds