Home » SQL & PL/SQL » SQL & PL/SQL » Group function (shows a duplicate value)
Group function [message #275023] Thu, 18 October 2007 02:28 Go to next message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
Hi all,
I have one table name is test.

sample data for test:

id name

1 michael
2 holding.

I need the status of dupicate values. if duplicate values present means it shows 'YES' or 'NO'..
I got output if dupicate value present
I did'nt get output for if duplicate value not present

What i try :

select case when count(id)>0 then 'YES' else 'NO' end as status
from test
group by id
having count(*)>1;

it shows no rows returned
i did'nt get output for this but i try like

select case when sum(count(id))>0 then 'YES' else 'NO' end as status
from test
group by id
having count(*)>1;

i got output....like 'NO' what is the reason

what is difference between above query and below query

Thanks
Michael
Re: Group function [message #275032 is a reply to message #275023] Thu, 18 October 2007 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Exercise: change "case when sum(count(id))>0 then 'YES' else 'NO' end" to "case when sum(count(id))=0 then 'NO' else 'YES' end"

What do you get? Analyze.

Regards
Michel
Re: Group function [message #275078 is a reply to message #275023] Thu, 18 October 2007 04:13 Go to previous messageGo to next message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
I got output like YES answer...
I analyze it but i have another questions regarding that.
I execute sum(count)>0 without case statements but it returns
No ROWS returned.How it reurn yes with case functions

Re: Group function [message #275082 is a reply to message #275078] Thu, 18 October 2007 04:16 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

I analyze it

And what is the result of your analysis?

Quote:

I execute...

Post (copy and paste) what you tried and don't forget to format: read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel
Previous Topic: Materialized View
Next Topic: mutating problem
Goto Forum:
  


Current Time: Tue Dec 06 08:39:09 CST 2016

Total time taken to generate the page: 0.12173 seconds