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

Related Articles

dynamic selection of SQL in the nested selection

Let's say that I have a tables tasks, projects, and work_items, all of which have a column fields containing a json object of custom values. Now lets say I want to write a function to query an arbitrary table for its field names. CREATE OR REPLACE FU

Using SQL Result Variables in the Nested SELECT Statement

I'm really unsure if the title of this is what I am trying to say, but it's the closest I could come. What I want to do is take the initial results from a query and use them in a nested Select statement within an INSERT statement. Here is my example:

How to remove columns that have zero sum in the list of files

Below is the code for applying PCA on multiple dataframes country wise stored in myfiles. ## Get file names for a working directory ### temp = list.files(pattern="*.csv") ## Read files ### myfiles = lapply(temp, read.csv) ### Name the files ###

How to select max Id from the nested selection?

I want to select max Id from nested select. Here is the my query: select max(Id) from (SELECT TOP 100 Id FROM [MyTable]) t This select should return 100, but it actually returns max id of MyTable. P.S. MyTable contains 100 000 records, so query retur

Presentation of the nested selection for ORM

I'm reading the article explaining the nested-loop-join algorithm and I don't exactly understand the actual working principle of nested selects. Here is an example provided by the article: The examples search for employees whose last name starts with

The nested selection does not accept more than one column

I have a few tables like this: departments dept_no char(4) primary key dept_name varchar(40) unique dept_emp emp_no int primary key dept_no char(4) primary key from_date date to-date date dept_manager dept_no char(4) primary key emp_no int primary ke