I think you're mixing this up with the other SQL thread. The OP was a little puzzled by GROUP behavior and what he wants, I think, is to identify duplicate records. He didn't say what he wants to do with the duplicates. I just suggested the count and having so he could get a feel for how many dupes are there.
-- rk -----Original Message----- From: ProfoxTech <[email protected]> On Behalf Of Stephen Russell Sent: Friday, March 30, 2018 10:33 AM To: [email protected] Subject: Re: Unexpected results from a group by clause The having clause allows the query to limit the output of the data set, it doesn't refocus on the raw data for additional processing. select PO.[BuyFromBuinessPartner], PO.[PurchaseOrder], PO.[Division], POL.[Item], POL.[Price] -- all items needed in group by , sum( POL.[OrderedQuantity]) totalQuant -- reason for the group by from [dbo].[FactPurchaseOrders] PO left join [dbo].[FactPurchaseOrderLines] POL on PO.[PurchaseOrder] = POL.[PurchaseOrder] and PO.[Division]= POL.[Division] where PO.Division ='RCT' and PO.OrderDate between '1-1-2018' and '4-1-2018' group by PO.[BuyFromBuinessPartner], PO.[PurchaseOrder], PO.[Division],POL.[Item], POL.[Price] Having sum( POL.[OrderedQuantity]) > 100000 -- Just show me anything over 100000 in quantity HTH On Fri, Mar 30, 2018 at 6:34 AM, Richard Kaye <[email protected]> wrote: > You can also add COUNT(*) to your query to see how many rows are > duplicated and use a HAVING to see just the duplicated rows. > > -- > > rk > > -----Original Message----- > From: ProfoxTech <[email protected]> On Behalf Of > [email protected] > Sent: Friday, March 30, 2018 12:37 AM > To: [email protected] > Subject: Re: Unexpected results from a group by clause > > On 2018-03-29 23:00, Joe Yoder wrote: > > I have an SQL select statement that includes a group by clause to order > > the > > output for reporting. It gets its data from a table that potentially > > includes legitimate duplicate records. > > > > SELECT account, date, memo, paid_amoun; > > FROM QB; > > GROUP BY account, date, memo, paid_amoun; > > INTO CURSOR det > > > > I happened to discover that the output of the select statement does not > > include duplicate records. Is this expected behavior? If so , how > > should > > one group data with duplicate records? > > > > Thanks in advance, > > > > Joe > > > Hi Joe, > > Replace the GROUP BY with the ORDER BY clause and it won't drop any > records. > > hth, > --Mike > > [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/dm5pr10mb1244c12fe92c3d9afd94cd02d2...@dm5pr10mb1244.namprd10.prod.outlook.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
