Cool example, Dawn! David
David Blocker [EMAIL PROTECTED] 781-784-1919 Fax: 781-784-1860 Cell: 339-206-0261 ----- Original Message ----- From: "Dawn Oakes" <[EMAIL PROTECTED]> To: "RBG7-L Mailing List" <[email protected]> Sent: Wednesday, April 13, 2005 1:45 PM Subject: [RBG7-L] - RE: Report sorting by count Dan, To expand on David Blocker's example: (BTW - he is the SQL King, so he may have a better example than mine below!) using Concomp Transmaster table If I want to see the details of each record in transmaster and get a count of how many transactions a customer has, it's a little more complicated. Select transid, custid, invoicetotal, count(*) fro transmaster group by transid, custid, invoicetotal Gives me one row for each unique transid, custid, invoicetotal and a count of the occurences of each of those rows, which is always going to be 1. (unless I have the same EXACT invoice in the system more than once.) select t1.TransID,t1.CustID,t1.InvoiceTotal, count(*) from transmaster t1, transmaster t2 where t1.custid = t2.custid group by t1.transid, t1.custid, t1.invoicetotal Gives me a list of all the transactions in transmaster with the number of transactions for each custid. The where clause kind of creates a group of it's own by limiting the count to where custid=custid. Dawn -----Original Message----- From: Dan Goldberg [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 13, 2005 12:46 PM To: RBG7-L Mailing List Subject: [RBG7-L] - Report sorting by count BlankI have a report with some breaks that I can sort by columns. But I need it to sort by the number of occurrences. Does anybody have any ideas on how to do this?? Dan Goldberg
