>> Todd <[EMAIL PROTECTED]> wrote:
>>>
>>> <cfquery datasource="#Application.Read#" name="qryGetRepeatSales">
>>> SELECT *, COUNT(*) AS totalcount, SUM(Cost) AS totalcost
>>> FROM orders, orderitems
>>> WHERE orders.OrderID = orderitems.OrderID
>>> AND OrderDate >= '#DateFormat(StartTime, "yyyy-mm-dd")#'
>>> GROUP BY UserID
>>> HAVING totalcount > 1
>>> </cfquery>
> It does. On the first line I'm doing a SELECT *. that would include the
> OrderDate column, would it not?
Let me guess, you are using MySQL.
In standard SQL that would not include the OrderDate column
because each field you select without aggregating it needs to be
in the list of fields you group by. So in standard SQL this query
can not produce any fields other then totalcount, totalcost (both
aggregates) and UserID (group by).
For some reason in MySQL this braindead syntax is allowed, but
even the manual says the result may be 'unpredictable', which
appears to be exactly what you are encountering:
http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html
It is better to stay away from non standard SQL unless you
understand every detail of the consequences.
Jochem
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
