Skip to Content
0
Former Member
Mar 19, 2013 at 03:03 PM

Doubt about group by and having

24 Views

Any

one Please help me.

I have 5 columns.

Sender Con ID Document Relationship Status xxx 12345 DOCTYPE sender DONE xxx 12345 DOCTYPE receiver DONE xxx 45678 DOCTYPE sender DONE xxx 45678 DOCTYPE receiver DONE xxx 56789 DOCTYPE sender DONE xxx 67890 DOCTYPE sender DONE xxx 55522 DOCTYPE receiver DONE

Here I want to retrieve only count(Relationship)=1.

I put the query as follows.

SELECT Table2.Sender,
Table1.Con ID,

Table3.Document,

Table4.Relationship,

Table1.Status

FROM Table1,

Table2,

Table3,
Table4,

Table5

WHERE
(
Table3.Document = 'DOCTYPE'
AND
Table4.Relationship IN ( 'sender','receiver')
)
AND
Table5.Receiver IN ( 'xxx' )
AND
Table1.Status = 'Done'
AND
(
Table1.Doctimestamp >= To_Date(To_Char(sys_extract_utc(systimestamp),'MM-DD-YYYY HH24:MI:SS'),'MM-DD-YYYY HH24:MI:SS') - interval '24' hour
AND
Table1.Doctimestamp <= To_Date (To_Char(sys_extract_utc(systimestamp),'MM-DD-YYYY HH24:MI:SS'),'MM-DD-YYYY HH24:MI:SS')
)

GROUP BY

Table2.Sender,

Table1.Con ID,

Table3.Document,

Table4.Relationship,

Table1.Status

HAVING (COUNT(Table1.Con ID)=1) AND Table4.Relationship != 'receiver'

This is the query I put.

Here Relationship is different. so it retrieve all. I need only one row which is Relationship = 'sender'

And also here shows the error that "not a group by function"

Thanks.