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
On Sun, Oct 4, 2009 at 4:31 AM, Michael <proxi...@land.ru> wrote:
> Hello,
>
> I have following SELECT statement in my program (Delphi+sqlite3):
>
> _________________________________________________________________________________________
> select
> id,power,
> (select sum
> ((strftime("%s",table2.endtime)-strftime("%s",table2.begintime))*table2.power)
> from table2 where table1.id=table1_id),
> (
> select min(point) from points where points.table1_id=table1.id and
> point >=
> (select sum
> ((strftime("%s",table2.endtime)-strftime("%s",table2.begintime))*table2.power)
> from table2 where table1.id=table2.table1_id)
> ),
>
> (
> (
> select min(point) from points where points.table1_id=table1.id
> and point >=
> (select sum
> ((strftime("%s",table2.endtime)-strftime("%s",table2.begintime))*table2.power)
> from table2 where table1.id=table2.table1_id )
> )
> -
> (select sum
> ((strftime("%s",table2.endtime)-strftime("%s",table2.begintime))*table2.power)
> from table2 where table1.id=table1_id)
> ) / power
> from table1
> where standing=1
> group by table1.id
> order by table1.id
> ________________________________________________________________________________________
>
> This statement contains text that is repeated 4 times:
>
> (select sum
> ((strftime("%s",table2.endtime)-strftime("%s",table2.begintime))*table2.power)
> from table2 where table1.id=table2.table1_id)
>
> How to reduce this statement and avoid double calculations?
>
>
> Thanks,
> Michael.
> _______________________________________________
> 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
