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 profoxtech-bounces@leafe.com On Behalf Of Stephen Russell Sent: Friday, March 30, 2018 10:33 AM To: profoxtech@leafe.com 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 rkaye@invaluable.com 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 profoxtech-bounces@leafe.com On Behalf Of mbsoftwaresolutions@mbsoftwaresolutions.com Sent: Friday, March 30, 2018 12:37 AM To: profoxtech@leafe.com 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]