Because not doing so violates the SQL standard. Allowing you to included non aggregated columns in the SELECT list is a non standard MySQL extension to the SQL language. You will get an error in other products, such as oracle, where you will get a "xxx is not a group by expression" error.
Information on this "feature" is here: http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html On Mon, 9 Aug 2004 17:22:17 -0600, Renà Fournier <[EMAIL PROTECTED]> wrote: > Thanks, the LEFT JOIN worked. > > I do have a question though, why is it considered best practice to list > all non-aggregated columns ( I assume you mean columns from trucks.*) > in the GROUP BY statement? I ask because I am interested in fast, > secure, standards-compliant code, I'm just not always sure what that > is. :-) > > ....Renà > > --- > Renà Fournier, > www.renefournier.com > > > > On Aug 9, 2004, at 2:14 PM, [EMAIL PROTECTED] wrote: > > > You need to change your INNER JOIN to a LEFT JOIN > > > > SELECT > > trucks.id, sum(history.time_sec) as total_seconds > > FROM > > trucks > > LEFT JOIN > > history > > ON trucks.id = history_truckid > > GROUP BY > > trucks.id > > ORDER BY > > total_seconds desc > > > > One other issue --->IMHO, the SQL engine is being too kind when it > > allows > > you to execute a query like "SELECT trucks.* .... GROUP BY ....". In > > practically EVERY OTHER SQL-based product you will use, you will be > > required to list _all_ non-aggregated columns in your GROUP BY > > statement > > or you will get an error. Listing every column you want to group on is > > considered "proper SQL format" and I highly recommend the practice. > > > > If you still want to see everything from your trucks table (like in > > your > > original query) you can do this: > > > > CREATE TEMPORARY TABLE tmpTruckIDs > > SELECT > > trucks.id, sum(history.time_sec) as total_seconds > > FROM > > trucks > > LEFT JOIN > > history > > ON trucks.id = history_truckid > > GROUP BY > > trucks.id > > ORDER BY > > total_seconds desc; > > > > SELECT trucks*, tmpTruckIDs.total_seconds > > FROM trucks > > INNER JOIN tmpTruckIDs > > ON tmpTruckIDs.id = trucks.id; > > > > DROP TABLE tmpTruckIDs; > > > > HTH, > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > > > Renà Fournier <[EMAIL PROTECTED]> wrote on 08/09/2004 03:56:58 PM: > > > >> I am having a problem building a SELECT statement that joins two > >> tables > >> with a WHERE condition. > >> > >> > >> SELECT > >> trucks.* > >> > >> FROM > >> trucks, history > >> > >> WHERE > >> trucks.account_id = '100' > >> AND trucks.status = 'Active' > >> AND history.truck_id = trucks.id <<<< This is the > >> tricky bit > >> > >> GROUP BY > >> trucks.id > >> > >> ORDER BY > >> history.time_sec DESC > >> > >> > >> Simply put (or as simply as I can put it :-) , this SELECT should > >> return all trucks in order of their activity (history.time_sec). The > >> problem is when a truck is new to the system and does not have a > >> single > >> record in the history table (and therefore no value for > >> history.time_sec). In that case, the truck is excluded from the > >> SELECTed rowsâbut I want it returned, just at the bottom of the list > >> (least active). > >> > >> Any ideas how this can be done? > >> > >> ...Rene > >> > >> -- > >> MySQL General Mailing List > >> For list archives: http://lists.mysql.com/mysql > >> To unsubscribe: > >> http://lists.mysql.com/[EMAIL PROTECTED] > >> > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
