Yup, it's like a "where" but it acts kind of after the grouping so you
can use aggregate functions to limit the output.
On 30/03/18 11:32, Stephen Russell wrote:
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/[email protected]
** 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.
