Steve Koterski wrote:
> On Fri, 07 Aug 1998 15:43:23 -0700, Richard Grossman
> <rgross...@techIII.com> wrote:
> >Actually, Roman, I have used SQL in another product and it allowed GROUP BY
> >without grouping on every single non-aggregate field. Also, there is no
> >conceptual reason I can see why this should be so, and it is a minor PITA in
> >Delphi local SQL.
> As far as I have seen, this is standard SQL. For a column to be allowed in
> the SELECT clause, it must either be the subject of an aggregate function
> or also be listed in the GROUP BY clause. And -- to me -- this makes a lot
> of sense. Take the dataset below, for example:
> State Name Amount
> ----- ---- ------
> CA Stacey Green 99.01
> CA Mike Brown 100.23
> CA Lila Prose 45.99
> CA Richard Grossman 50.95
> Assume an SQL statement like this:
> SELECT N.State, SUM(N.Amount)
> FROM Names N
> GROUP BY N.State
> ORDER BY N.State
> If you execute an aggregating query, summing the Amount column and grouping
> on State, the above four records would be combined into one aggregated
> record for the State column value "CA", because all four records share this
> group value. The values in the Amount column would be added together to
> derive a single value in this one record. But what of the values in the
> Name column? Which name (Stacey, Mike, Lila, or Richard) gets included in
> the result set? Unless you use one of the aggregate functions like MIN or
> MAX, there is no realistic means by which to determine this.
> >For example:
> >SELECT
> >invoice.customer_id,
> >sum(invoice.amount) as total_sales
> >trim(customer.firstname) + ' ' + trim(customer.lastname) as cust_name
> >FROM
> >invoice, customer
> >WHERE
> >customer.id = invoice.customer_id
> >GROUP BY
> >invoice.customer.id
> >There's absolutely no reason why I should have to also include the "baggage"
> >fields firstname and lastname in the group-by, since the customer.id uniquely
> >identifies them.
> This is a slightly different case, where there is a direct one-for-one
> correlation between a given Customer_ID and the associated name. For any
> single Customer_ID, there will always only be one name (one distinct
> combination of FirstName and LastName) -- in the same record.
> But the SQL parser would still require the FirstName and LastName columns
> be in the GROUP BY clause if they are in the SELECT clause and not the
> subject of an aggregate function. This is because there is the *potential*
> that there might not be a direct correlation. Without making a preliminary
> first-pass on the data to determine whether such a lack of correlation
> exists, a one-pass SQL system must enforce such rules in the SQL statement.
> >Another thing: Delphi local SQL won't let me use an AS field name, I have to
> >repeate the whole darn expression, so in the example above, I have to have:
> >GROUP BY
> >invoice.customer.id,
> >trim(customer.firstname) + ' ' + trim(customer.lastname)
> >instead of
> >GROUP BY
> >invoice.customer.id,
> >cust_name
> >or even better
> >GROUP BY
> >invoice.customer.id
> >Not a huge problem or anything, just a minor annoyance....
> This is true. Column correlation names cannot be used directly in a GROUP
> BY clause. You must use the actual column name. Use of a column correlation
> name (or number prepresenting the ordinal position of the column) does work
> in the ORDER BY clause. Hence the two acceptable statements below (which
> use the sample Paradox table Orders.db).
> SELECT O.SaleDate AS TheDate, SUM(O.ItemsTotal)
> FROM "Orders.db" O
> GROUP BY O.SaleDate
> ORDER BY O.TheDate
> SELECT O.SaleDate AS TheDate, SUM(O.ItemsTotal)
> FROM "Orders.db" O
> GROUP BY O.SaleDate
> ORDER BY 1
> The inability to use column correlation names or derived values in a GROUP
> BY clause are nothing more than simple shortcomings in the local SQL
> engine. I have written these up as suggested improvement changes for the
> BDE.
> //////////////////////////////////////////////////////////////////////////
> Steve Koterski "Knowledge comes, but wisdom lingers."
> Technical Publications -- Alfred, Lord Tennyson (1809-1914)
> INPRISE Corporation
> http://www.inprise.com/delphi
