OraFAQ Forum: SQL & PL/SQL » Group function

Home » SQL & PL/SQL » SQL & PL/SQL » Group function (shows a duplicate value)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Group function [message #275023]

Thu, 18 October 2007 02:28

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

Report message to a moderator

Re: Group function [message #275032 is a reply to message #275023]

Thu, 18 October 2007 02:35

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Group function [message #275078 is a reply to message #275023]

Thu, 18 October 2007 04:13

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

Report message to a moderator

Re: Group function [message #275082 is a reply to message #275078]

Thu, 18 October 2007 04:16

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	Materialized View
Next Topic:	mutating problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 08 05:58:17 CST 2024