Home » SQL & PL/SQL » SQL & PL/SQL » except duplicate record
except duplicate record [message #642455] Fri, 11 September 2015 02:52 Go to next message
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 #642456 is a reply to message #642455] Fri, 11 September 2015 03:00 Go to previous messageGo to next message
Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
select count(distinct xyz)
Re: except duplicate record [message #642457 is a reply to message #642456] Fri, 11 September 2015 03:11 Go to previous messageGo to next message
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 #642458 is a reply to message #642457] Fri, 11 September 2015 03:15 Go to previous messageGo to next message
Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh, right! None of the duplicates should be counted. My bad.
Re: except duplicate record [message #642461 is a reply to message #642458] Fri, 11 September 2015 05:26 Go to previous messageGo to next message
Nasir.azeem
Messages: 40
Registered: September 2014
Location: Karachi
Member
Thanks Cookiemonster Smile
Re: except duplicate record [message #642462 is a reply to message #642457] Fri, 11 September 2015 06:14 Go to previous messageGo to next message
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 #642541 is a reply to message #642455] Mon, 14 September 2015 04:33 Go to previous messageGo to next message
aspire
Messages: 18
Registered: September 2015
Location: TVN
Junior Member
Have table t1 with two columns names are A,B and following datas.
A B
------------
1 10
2 20
3 30
4 40
3 30
4 40

select count(a) from t1 where a not in(
select a from t1 group by a having count(a)>1);
Re: except duplicate record [message #642543 is a reply to message #642541] Mon, 14 September 2015 04:43 Go to previous messageGo to next message
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's exactly the same as my first query except you've added a pointless second column that the OP doesn't have.
Re: except duplicate record [message #642555 is a reply to message #642543] Mon, 14 September 2015 06:25 Go to previous messageGo to next message
aspire
Messages: 18
Registered: September 2015
Location: TVN
Junior Member
Give space to answer questions for junior members.
Re: except duplicate record [message #642563 is a reply to message #642555] Mon, 14 September 2015 07:25 Go to previous message
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.
Previous Topic: Display the range wise column names
Next Topic: Using constants
Goto Forum:
  


Current Time: Tue Jul 28 22:25:34 CDT 2026