| except duplicate record [message #642455] |
Fri, 11 September 2015 02:52  |
 |
Nasir.azeem
Messages: 40 Registered: September 2014 Location: Karachi
|
Member |
|
|
Hi every one.
i have a table abc with only one column xyz.
now i have data in abc (in number) 1 to 500.
after while i insert 1 to 10 again.
it mean 1 to 10 is duplicate data.
when i query the table as
select count(*) from abc then
it is 510 record its ok.
but
now i want to count(*) from abc (except duplicate) record which is answer is 490 record..... (Except Duplicate record)
[Updated on: Fri, 11 September 2015 02:54] Report message to a moderator
|
|
|
|
|
|
| Re: except duplicate record [message #642457 is a reply to message #642456] |
Fri, 11 September 2015 03:11   |
cookiemonster
Messages: 13975 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
that would give 500.
Here's a couple of ways:
SELECT COUNT(*)
FROM (SELECT xyz
FROM abc
GROUP BY xyz
HAVING COUNT(*) = 1
);
SELECT COUNT(*)
FROM abc
WHERE xyz NOT IN (SELECT xyz
FROM abc
GROUP BY xyz
HAVING COUNT(*) > 1
);
|
|
|
|
|
|
|
|
| Re: except duplicate record [message #642462 is a reply to message #642457] |
Fri, 11 September 2015 06:14   |
John Watson
Messages: 9003 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
The CBO does a much better job with your first solution:orclz> create table abc(xyz number);
Table created.
orclz> insert into abc(select rownum from dual connect by level < 501);
500 rows created.
orclz> insert into abc(select rownum from dual connect by level < 11);
10 rows created.
orclz> commit;
Commit complete.
orclz> set autot trace exp
orclz> SELECT COUNT(*)
2 FROM (SELECT xyz
3 FROM abc
4 GROUP BY xyz
5 HAVING COUNT(*) = 1
6 );
Execution Plan
----------------------------------------------------------
Plan hash value: 1018226419
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 (25)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 5 | | 4 (25)| 00:00:01 |
|* 3 | FILTER | | | | | |
| 4 | HASH GROUP BY | | 5 | 20 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| ABC | 510 | 2040 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(COUNT(*)=1)
orclz> SELECT COUNT(*)
2 FROM abc
3 WHERE xyz NOT IN (SELECT xyz
4 FROM abc
5 GROUP BY xyz
6 HAVING COUNT(*) > 1
7 );
Execution Plan
----------------------------------------------------------
Plan hash value: 3850865817
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 7 (15)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | HASH JOIN ANTI NA | | 485 | 8245 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL | ABC | 510 | 2040 | 3 (0)| 00:00:01 |
| 4 | VIEW | VW_NSO_1 | 25 | 325 | 4 (25)| 00:00:01 |
|* 5 | FILTER | | | | | |
| 6 | HASH GROUP BY | | 25 | 100 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL| ABC | 510 | 2040 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("XYZ"="XYZ")
5 - filter(COUNT(*)>1)
|
|
|
|
|
|
|
|
|
|
| Re: except duplicate record [message #642563 is a reply to message #642555] |
Mon, 14 September 2015 07:25  |
cookiemonster
Messages: 13975 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
|
If you'd shown a different way of doing it I wouldn't have said anything, but you didn't and even worse you changed the problem - that's not helpful. If the OP has one column, adding a second, with no information about how it relates to the existing column/data, is less than helpful.
|
|
|
|