have a sum in the nested selection

advertisements

I have two tables: one is Tour and other is ReservedTour. The columns of the tables are:

Tour       | ReservedTour
===========|=============
id         | id
city       | Tid
capacity   | number
timeout    | .
.          | .
.          | .
.          |
========== |======

I write an SQL statement such as

select *, (select sum(rt.`number`) from ReservedTour as rt where rt.`Tid`=t.id GROUP BY rt.`Tid`) as total
  from Tour as t
 where City = 'alahom' and '1400-12-13' <= t.`timeout` and 4 < t.`Capacity`- total;

but this has an error — the total is not correct.

How can I correct this?


Try this:

select t.*, sum(rt.`number`) as total
from Tour as t
join  ReservedTour as rt on rt.`Tid`=t.id
where City = 'alahom' and  t.`timeout`=> '1400-12-13' and t.`Capacity`-total > 4
GROUP BY t.id