Home » Other » Training & Certification » Query for joining columns of single table
Query for joining columns of single table [message #261396] Wed, 22 August 2007 08:53 Go to next message
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
Re: Query for joining columns of single table [message #261400 is a reply to message #261396] Wed, 22 August 2007 08:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
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
Re: Query for joining columns of single table [message #261405 is a reply to message #261400] Wed, 22 August 2007 09:08 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
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
Re: Query for joining columns of single table [message #261436 is a reply to message #261431] Wed, 22 August 2007 10:35 Go to previous message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

OK Michel, will do as suggested.

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


Current Time: Fri Dec 02 21:04:37 CST 2016

Total time taken to generate the page: 0.10047 seconds