Unexpected results from a group by clause

[Joe Yoder]

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


--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---

_______________________________________________
Post Messages to: ***@leafe.com
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/CABQeDnUC+QOHyHAkr_eCXL7UmXBqrt1FrrLi7Po-F36MeSO=***@mail.gmail.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.

[Vince Teachout]

Joe,

Use the Order by Clause to set your ordering. Group by will, by design,
eliminate duplicates. GROUP BY is used for aggregate functions, such as
sum(), count() etc.

Post by Joe Yoder
I have an SQL select statement that includes a group by clause to order the
output for reporting. ...
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?

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus


_______________________________________________
Post Messages to: ***@leafe.com
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/1162876c-48f1-18da-70d0-***@taconic.net
** 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.

[m***@mbsoftwaresolutions.com]

Post by Joe Yoder
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

_______________________________________________
Post Messages to: ***@leafe.com
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/***@mbsoftwaresolutions.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.

[Richard Kaye]

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-***@leafe.com> On Behalf Of ***@mbsoftwaresolutions.com
Sent: Friday, March 30, 2018 12:37 AM
To: ***@leafe.com
Subject: Re: Unexpected results from a group by clause

Post by Joe Yoder
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


_______________________________________________
Post Messages to: ***@leafe.com
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/***@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.

[Stephen Russell]

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

Post by Richard Kaye
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-----
Sent: Friday, March 30, 2018 12:37 AM
Subject: Re: Unexpected results from a group by clause

Post by Joe Yoder
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: ***@leafe.com
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/CAJidMYJmk2esPp-1KLg9E4RWVyJNsPs1qgsnc3G0cB=***@mail.gmail.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.

[Richard Kaye]

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-***@leafe.com> On Behalf Of Stephen Russell
Sent: Friday, March 30, 2018 10:33 AM
To: ***@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

Post by Richard Kaye
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-----
Sent: Friday, March 30, 2018 12:37 AM
Subject: Re: Unexpected results from a group by clause

Post by Joe Yoder
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: ***@leafe.com
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/***@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.

[Ricardo Araoz]

Yup, it's like a "where" but it acts kind of after the grouping so you
can use aggregate functions to limit the output.

Post by Stephen Russell
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

Post by Richard Kaye
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-----
Sent: Friday, March 30, 2018 12:37 AM
Subject: Re: Unexpected results from a group by clause

Post by Joe Yoder
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: ***@leafe.com
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/a28b4101-6f59-277a-2a1b-***@gmail.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.

[Jürgen Wondzinski]

HAVING is basically a Post-Processing of the SQL-Result. Therefor HAVING
uses the newly defined column names, not the original field names:

SELECT ZipCode, count(*) AS HowMany FROM Adresses GROUP BY 1 WHERE State =
"TX" HAVING HowMany > 1000



-----Ursprüngliche Nachricht-----
Von: ProFox <profox-***@leafe.com> Im Auftrag von Ricardo Araoz
Gesendet: Freitag, 30. März 2018 22:40
An: ***@leafe.com
Betreff: Re: Unexpected results from a group by clause

Yup, it's like a "where" but it acts kind of after the grouping so you
can use aggregate functions to limit the output.




_______________________________________________
Post Messages to: ***@leafe.com
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/004901d3c87a$4e525420$eaf6fc60$@wondzinski.de
** 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.

[Ted Roche]

GROUP BY is intended to GROUP multiple records together so you can
calculate MAX(), MIN(), SUM() and similar "aggregate" functions on
multiple records and report them in one line.

ORDER BY specifies the ORDER of the resulting recordset.

It's just a confusing coincidence that VFP usually reports records
that are GROUPed in the correct order. Don't count on that, as it's
not part of the SQL standard to do that, and some data engines don't.

Post by Joe Yoder
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
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---

[excessive quoting removed by server]

_______________________________________________
Post Messages to: ***@leafe.com
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/CACW6n4sx76Zni-K0t+AvzsH5dFiKhz1Nci5CHaC8OQHNs1zQ-***@mail.gmail.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.