OraFAQ Forum: Training & Certification » Query for joining columns of single table

Home » Other » Training & Certification » Query for joining columns of single table

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

Query for joining columns of single table [message #261396]

Wed, 22 August 2007 08:53

abhitabh.jamankar
Messages: 4
Registered: August 2007
Location: India

Junior Member

Hi all,
i have one table (sender, receiver , error)
error having (SUCCESS and ERROR) values
like
sender receiver error
--------- ----------- --------
PO TOM SUCCESS
PO TOM SUCCESS
DICK JOY ERROR
DICK JOY ERROR
DICK JOY SUCCESS
PO TOM ERROR
DICK JOY ERROR
DICK JOY ERROR
PO TOM SUCCESS
DICK JOY SUCCESS

now i want do display SUCCESS and ERROR values in different columns
like

sender receiver OK ERR
--------- ----------- ----- ------
PO TOM 3 1
DICK JOY 2 4

Thanks

Report message to a moderator

Re: Query for joining columns of single table [message #261400 is a reply to message #261396]

Wed, 22 August 2007 08:58

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You have to use count/sum, group by, decode/case.
Post what you tried, where you are stuck.
We help to do homeworks but don't do them.

If you post a test case (create table and insert statements for the example), it will help us help you.

Also, read and follow How to format your posts.
Make sure that lines of code do not exceed 80 or 100 characters when you format. Use the "Preview Message" button.

Regards
Michel

Report message to a moderator

Re: Query for joining columns of single table [message #261405 is a reply to message #261400]

Wed, 22 August 2007 09:08

Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata

Senior Member

Try this:

select sender,receiver,count(ok) as ok,count(err) as err from
(select  sender,
        receiver,
        case when error='SUCCESS' then 1 else 0 end ok,
        case when error='ERROR' then 1 else 0 end ERR 
from data)
group by sender,receiver

Cheers
Soumen

[Updated on: Wed, 22 August 2007 09:11]

Report message to a moderator

Re: Query for joining columns of single table [message #261431 is a reply to message #261405]

Wed, 22 August 2007 10:20

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

In your case you have to use SUM and not COUNT.
Please, do not post solution of homework, help the poster find the solution.
Give him clue but not solution, he will learn much more.

If you have a better solution afterwards, then post it.

Regards
Michel

Report message to a moderator

Re: Query for joining columns of single table [message #261436 is a reply to message #261431]

Wed, 22 August 2007 10:35

Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata

Senior Member

OK Michel, will do as suggested.

Cheers
Soumen

Report message to a moderator

Previous Topic:	Return all Saturday and Sundays between two specified date range
Next Topic:	want to prepare for the OCA for developers

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 18:48:35 CDT 2024