Home » SQL & PL/SQL » SQL & PL/SQL » regarding count (oracle 9i)
regarding count [message #287179] |
Tue, 11 December 2007 05:59  |
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   |
pablolee
Messages: 2882 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   |
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   |
pablolee
Messages: 2882 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  |
|
@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.
|
|
|
Goto Forum:
Current Time: Fri Feb 14 16:39:19 CST 2025
|