| Counting unique values in a table [message #20419] |
Wed, 22 May 2002 13:21  |
Al Webre
Messages: 2
Registered: May 2002
|
Junior Member |
|
|
I know I've seen how to do this somewhere before - but can't think of the right combination to get it. I have FIELD1 in TABLE1.
I can do select unique FILED1 from TABLE1; and get 5 separate values.
I can also select count (*) from TABLE1; and get 53,276, the number of rows in the table.
I want to count the number of unique entries for FILED1 in TABLE1. I haven't been able to do it.
The output should look something like
Value1 300
Value2 277
Value3 23555
Value4 4866
Value5 333
Does anybody know how to construct the PL/SQL statement to do this?
|
|
|
|
|
|
| Re: Counting unique values in a table [message #20457 is a reply to message #20419] |
Sat, 25 May 2002 10:33  |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
Since you are not so clear and I am not sure what your problem exactly is, let me put it out what I understand and my solution is.
You say, you want to count the number of unique entries for FIELD1 in TABLE1. Unique means, you mean, distinct, which a value occurred only once and not repeated any more in the other part of table? If so, then why do you want to count it, as the meaning itself says, it occurs only once and the counter result is only one?
OR
otherwise, are you talking about returning the rows which have unique values and not that repeated/reoccurred any more in the other part (rows) of TABLE1? If so, I can give you a solution.
For example, assume the table TABLE1 has only one column FIELD1 and the values exist for it are Value1, Value2, Value3, Value4, and Value5. Except Value2 and Value4, all other values Value1, Value3 and Value5 have 5 records each (totalling to 15 (3*5)). Same time, there are two more records in the table for Value2 and Value4, each occurred only once in the table. So now the table has a total of 17 (5 records of each Value1, Value3 and Value5 + two unique records of Value2 and Value4).
SELECT FIELD1 FROM TABLE1 GROUP BY FIELD1 HAVING COUNT(FIELD1)=1;
You can put this as a subquery when your table has more than one column and any of the columns are not participating in GROUP BY clause of your SELECT statement (as you may be aware of that, a SELECTion of column which is not listed in GROUP BY clause, is not allowed in ORACLE and it returns an error stating 'not a GROUP BY expression').
For example,
SELECT ENO,ENAM,SALARY FROM EMPLOYEE_TABLE WHERE DEPT = ANY(SELECT DEPT FROM EMPLOYEE_TABLE GROUP BY DEPT HAVING COUNT(DEPT) = 1);
returns the info of employees those who are alone in the their respective departments.
Hope you understand the point. If you still need any help, mail me back, I would be happy to help you out.
Good luck :)
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
