Doubt about group by and having

[Former Member]

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.

[Former Member · Accepted solution]

Hi Simone,

Yeah sorry for that. All other results were helped me in some way and also learnt.

For my output, I modified Mohanraj Periasamy's query like below.

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')

   )

      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')

   )

   AND

   Tabl1.ConId in

                (SELECT

                    Table1.Con ID

                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 Table1.Con ID

having count(Table1.Con ID)=1

                   )

Thanks & Regards,

Sindhu

[former_member188911 · Accepted solution]

thank you Sindhu, i marked the answers accordingly

[Former Member]

Hi Friends,

Now I tried and got output .  Very Happy.

Thank you all for your help, replies and kind cooperation.

Will meet again

Thanks.

[former_member188911]

Hi Sindhu,

can you please mark which answers helped you solve the problem so that who helped is rewarded and who will need this in the future will know which ones worked.

thanks!

Simone

[Former Member]

This message was moderated.

[Former Member]

Hi Friends sorry for late reply..

@Rakesh

Rakesh, i did not mention where conditions here. sorry for that.
Below r the conditions.

( Table1.DOCTYPEID=Table3.TYPEID )
AND ( Table1.PARTNERID=Table2.SENDERID )
AND ( Table1.DOCID=Table4.DOCID )

and this is a webi report using custom sql and MAX functions not working here.

@MohanRaj , now i am trying your query. Let you know once i done.

Thanks:)

[Former Member]

This message was moderated.

[Former Member]

Hi Sindhu,

You can do this in two steps using sub-query. Try the below query

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')

   )

   AND

   Tabl1.ConId in

                (SELECT

                    Table1.Con ID

                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

                    Table1.Con ID

                having

                    count(case when Table4.Relationship='sender' then 1 end) =1 and count(case when Table4.Relationship='receiver' then 1 end) =0)

GROUP BY

    Table2.Sender,

    Table1.Con ID,

    Table3.Document,

    Table4.Relationship,

    Table1.Status

[Former Member]

Hi Rakesh & Sunil,

Thanks for ur reply.

@Rakesh: I put the query as you said but it shows "invalid identifier" error. Here PAR is instead of RN

@Sunil:

Sender	Con ID	Document	Relationship	Status
xxx	12345	DOCTYPE	sender	DONE
xxx	12345	DOCTYPE	receiver	DONE

Above table shows a pair. Here same sender, document, conid, status but different relationship.

I should get both sender and receiver in "where" with group by ConID. If anyone of the pair contains only one row then I want tat row. If i put query as my post

then it retreive all records not a single row because the relationship is different so it groups sender ans one row and receiver as one row. So as per the condition count(conid)=1 it retreives all data. But i want count(conid)=1 relationshiop=sender.

[former_member189638]

Are you creating this WebI report using custom SQL?? Did you try if this work on the Database side?

Surprisngly I did not see any table join conditions in your where clause, How are you joining the tables?

How about trying the below SQL. Not sure if this will work

SELECT Table2.Sender,
Table1.Con ID,

Table3.Document,

MAX(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,

Table1.Status

[former_member189638]

There are two piece of code below which I have highligted in bold. Please add this to your code.

SELECT Table2.Sender,
Table1.Con ID,

Table3.Document,

Table4.Relationship,

Table1.Status,

ROW_NUMBER() OVER (PARTITION BY Table1.Con ID ORDER BY Table4.Relationship DESC) AS RN

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')
   )

AND RN = 1

GROUP BY

Table2.Sender,

Table1.Con ID,

Table3.Document,

Table4.Relationship,

Table1.Status

[Former Member]

Hi Rakesh and Sunil,

Thanks for ur reply.

Wat u said is correct. But if i put lik Table4.Relationship  IN  ( 'sender','receiver'), then it will retrieve watever the records in sender and receiver. But i want only the records as count(Table4.Relationship)=1, which is, here ConID is common to a pair of relationship(sender & receiver).

I want lik this.

Sender	Con iD	Document	Relationship	Status
xxx	56789	DOCTYPE	sender	DONE
xxx	67890	DOCTYPE	sender	DONE

Here Con ID is common for a pair of relationship. I want only one row of Con ID and Relationship is sender.

i.e: COUNT(Table1.Con ID)=1 and Relationship = 'sender'. Please let me know for any clarifications.

Thanks

[Former Member]

Hi sindhu

well ,its sound confusing  you written a condition like count =1 and rltp =sender and u shows data with two rows , do you mean count(conid)>0 ? if it is then it will wrk as it showed in table

or still it is not helpful thn

can you explain in detail like show the data what you have got and show the data how you would  like to have 

Ragards

Sunil

[Former Member]

Hi

You havent used aggregated Function when using group by Function in select statement , add count(relationship) as count_relationship in select statement ,

And also rakesh pattani said  is correct , why to make query complex , if you use IN statement it retieves all the rows of both sender and receiver and there you are filtering relation ship notequal to

sender it doesnt makes sense.  Instead you write relationship=sender thats all

Hope this helps u

Regards

sunil

[former_member189638]

If you only want Sender then in the where clause itself you can just put Sender instead of

Table4.Relationship  IN  ( 'sender','receiver') ....right???