RE: Embedded SQL

Your right, I had it backwards in my feeble brain....I am not looking for 
grouping but rather a sort order.....thanks for allowing me to figure out 
how stupid I am today :)


On Mon, 12 Dec 2005 14:30:54 -0600, DeLong, Eric wrote
> Well, no...  That's the way SQL does it...
> 
> Think about what GROUP BY is doing.  When you use grouping, you are
> instructing SQL to expect many (or one) potential rows, to be 
> aggregated in some scalar function such as SUM(), AVG(), COUNT() and 
> so forth.  All other fields in the SELECT that do not use 
> aggregation must be considered as grouping values, and must be 
> declared in the GROUP BY list.  However, this is actually a good thing...
> 
> Think of the grouping fields like an implied index over a table, then
> reorder your GROUP BY fields so that your important values are 
> listed first, with the least important ones last....  Then the 
> results will be reordered as specified.
> 
> Eric DeLong
> Sally Beauty Company
> MIS-Project Manager (BSG)
> 940-297-2863 or ext. 1863
> 
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Douglas W. Palme
> Sent: Monday, December 12, 2005 1:50 PM
> To: Mid Range List
> Subject: Embedded SQL
> 
> Why does the system force me to include all fields in a group by statement?
> 
> I am pulling several fields including two calculated fields (margin 
> and margin pct), but by grouping by multiple fields I am not going 
> to see everything from low to high on the pct.
> 
> As an example, I am pulling the following fields:
> 
> invoice number
> invoice location
> sold to number
> gl number (revenue type)
> gl desc
> extended price
> extended cost
> margin (calculated)
> margin pct (calculated)
> 
> I need to group them by the inovice location so that the report does 
> a page break after a change in the location, I need to rank them 
> from low to high on the pct.
> 
> When I ran the query in STRSQL, it runs extremely fast (across a 
> logical) up
> 
> until I add in the group statement and then it required that I add 
> in all seven fields (with the exception of the calculated fields)
> ....which then throws off my grouping.....
> 
> Any suggestions ?????
> 
> If you bought it, it was hauled by a truck - somewhere, sometime.
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) 
> mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To 
> subscribe, unsubscribe, or change list options, visit: 
> http://lists.midrange.com/mailman/listinfo/midrange-l or email: 
> MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment 
> to review the archives at http://archive.midrange.com/midrange-l.
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) 
> mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To 
> subscribe, unsubscribe, or change list options, visit: 
> http://lists.midrange.com/mailman/listinfo/midrange-l or email: 
> MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment 
> to review the archives at http://archive.midrange.com/midrange-l.


If you bought it, it was hauled by a truck - somewhere, sometime.