RE: Embedded SQL

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

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.