--- paiva...@gmail.com wrote: From: Pavel Ivanov <paiva...@gmail.com> To: edz...@volcanomail.com, General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] double calculation in SELECT Date: Mon, 5 Oct 2009 08:39:53 -0400
> No, I did not assume unique pairs of id, power. Actually there is a small > syntax error in the original query, which is however tolerated by SQLite. The > original query essentially (with respect to this point) looks like: Yes, you assumed that, maybe unconsciously. ;-) Look, you've proposed to change from SELECT sum(...) FROM table2 WHERE table2.table1_id = table1.id to SELECT table1.id, sum(...) FROM table1, table2 WHERE table2.table1_id = table1.id GROUP BY table1.id So, if table1 has 2 rows with the same id then corresponding table2 rows will appear in the sum() twice and result of aggregation will be different from the original query... Pavel On Mon, Oct 5, 2009 at 8:33 AM, Edzard Pasma <edz...@volcanomail.com> wrote: > > > --- paiva...@gmail.com wrote: > > From: Pavel Ivanov <paiva...@gmail.com> > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Subject: Re: [sqlite] double calculation in SELECT > Date: Mon, 5 Oct 2009 07:13:28 -0400 > > First of all, do you really store several rows with the same id in the > table1? If no then you don't need 'group by' clause, if yes then I > hope you understand that 'power' returned in your query is undefined, > i.e. it can be returned from any row with this id - you can't control > which one of them. > And about the query: without some information about data stored in > your tables and its structure we cannot give you a useful help. For > example solution suggested by Edzard assumes that table1 has unique > pairs id-power, otherwise it will return different results from your > original query... > > Pavel > > ---------------------------------------------- > > Hello Pavel, > > No, I did not assume unique pairs of id, power. Actually there is a small > syntax error in the original query, which is however tolerated by SQLite. The > original query essentially (with respect to this point) looks like: > > SELECT id, power, .. > FROM table1 > GROUP BY id > > The column power is neither in the GROUP BY clause nor in an aggregate > function. I assume SQLite returns just a single row per id here, also if > there are more rows with the same id and different power. > > Other DBMS's do not accept this syntax and require to explicitly group by all > columns that occur outside of aggregate functions. SQLite is more tolerant. > Possibly that makes the unique sort faster. > > Edzard > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > Sorry, you were right. But I'm happy you noted the point as it is a peculiar part of the SQL syntax. I would have liked to leave out the second column (power) from the group by, assuming (indeed:-) that id is unique. But then I would hesitate to select the plain column, even though SQLite allows that. Formally one is forced to use some aggregate function e.g: SELECT t1.id, MAX (t1.power) FROM t1, .. GROUP BY t1.id But this is again ugly, as there is only one row to take the MAX of. IMHO and AFAIK the SQL standard misses a proper/propely named aggregate function for this case. I mean an ANY or FIRST function. Thanks, Edzard _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
