Help with query

[gmc1103]

I have a sub table (visitasturmas) whis those field

idTurma, idVisita, numberofstudents

The main table (visitas) have those fields

idVisita, amountVisit, totalPerStudend

So this is how it works

A teacher can organize study visit: To do this he needs the classes , and how many students of each classes go (since a visit can have several classes, then i have 2 tables

for instance, let suppose this configuration

 

idTurma, idVisita, numberofstudents

10 11 20

11 11 15

18 11 17

So i have 3 classes and i have 20+15+17 = 52 students

Now i want to remove 3 students from idTurma 18, so now i have 49 students

The main table keeps the id, the total amount of the study visit and total for each student

Imagine the total amount is 1000, so i must divide by 52 = 19,29 €

After removing students the value must change because now is not 52 studens but 49, so it is 20,40 €

I can't get this working with mysql

 

SELECT
    v.Total AS total,
    SUM(vt.numberofstudents) AS alunos,
    (total/alunos) AS final
FROM visitasturmas vt
     INNER JOIN visitas v
                on vt.idVisita = v.idVisita where v.idVisita = 398;

 

I get error

[42S22][1054] Unknown column 'alunos' in 'field list'

[Barand]

You can't reference alias in the field list part of the query (they aren't allocated until the later output phase of the query. For instance, you can order by final),

You need

SELECT
    v.Total AS total,
    SUM(vt.numberofstudents) AS alunos,
    (v.Total/SUM(vt.numberofstudents)) AS final
...

[edit] P.S.

To summarize

+---------+---------------------------+
|   I/O   |      Query Clauses        |
+---------+---------------------------+
|         |                           |
| INPUT   |    SELECT ...             | Column aliases
|         |    FROM ...               | defined but not
|         |    WHERE ...              | referenced
|         |                           |
+---------+---------------------------+
|         |                           |
|         |    GROUP BY ...           | Column aliases
| OUTPUT  |    HAVING ...             | can be referenced
|         |    ORDER BY ...           | here
|         |                           |
+---------+---------------------------+

 

[gmc1103]

Thank you Barand