> 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
