Dennis Try this:
I'm asuming that PURORD is the detail table and PURHDR is the header table sel t1.tr_acct t2.vn_id t1.tr_po (t1.tr_rcv * t1.tr_unitp) + fro purord t1, purhdr t2 whe + t1.tr_po = t2.tr_po and + t1.tr_code = "rx" and + t2.vn_id not cont "CON" and + t1.tr_inv is null and + tr_rdate >= "01/01/02" and + tr_rdate <= "04/26/02" + and t1.tr_po in (select tr_po from purord group by tr_po having sum (tr_rcv * tr_unitp) > "1000" ) David Blocker ----- Original Message ----- From: "Dennis Fleming" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, May 24, 2002 11:57 AM Subject: Group By and Having > The customer needed a "quick and dirty" report to show all PO's greater > than $1000 and the following does that. But, it doesn't list the line items > because of the group by and having. > > What I need is to list all the individual line items if the total value of > any PO is greater than $1000. (I have patriotism but falling a bit short on > Lustigism.) > > > sel t1.tr_acct t2.vn_id t1.tr_po sum (t1.tr_rcv * t1.tr_unitp) + > fro purord t1, purhdr t2 whe + > t1.tr_po = t2.tr_po and + > t1.tr_code = "rx" and + > t2.vn_id not cont "CON" and + > t1.tr_inv is null and + > tr_rdate >= "01/01/02" and + > tr_rdate <= "04/26/02" + > group by t1.tr_acct t2.vn_id t1.tr_po + > having sum (t1.tr_rcv * t1.tr_unitp) > "1000" > > TIA, > > Dennis > ***** > Dennis Fleming > IISCO > http://www.TheBestCMMS.com > Phone: 570 775-7593 > Fax: 570 775-9797 > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ > TO SEARCH ARCHIVES: > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
