CODEWITHSUNDEEP
sqlsqlite
DrTim
DrTim

Reputation: 203

How to use a parent query value in select statement of subquery

I have an aggregate query and a subquery and I want to use one of the values from the 'parent' query in a formula in the select statement for the subquery ...

Something like this, where I want to access that 'The_Total' alias in the subquery ...

SELECT some_field, COUNT () AS The_Total,
    (SELECT The_Total / 100 FROM Some_Table) AS The_Total_Div_100
FROM Some_Table
GROUP BY some_field

Would produce a result like this ...

some_field | The_Total | The_Total_div_100
------------------------------------------
Fred       |   100     |        1
Mary       |   200     |        2
Bob        |   300     |        3

The query I have is much more complicated than that with joins and where clauses etc but it is this ability to reference a parent query value in a subquery select formula that has me stuck. I have tried "SELECT (The_Total)" instead of just The_Total etc.

Thanks in advance.

Upvotes: 4

Views: 3590

Answers (2)

juergen d
juergen d

Reputation: 204766

You can't "access" aliases directly in the select clause or the where clause.

It is not allowable to refer to a column alias in a WHERE (or SELECT) clause, because the column value might not yet be determined when the WHERE clause is executed.

You need to write up that formula again.

SELECT some_field, 
       COUNT () AS Total,
       COUNT () * 100 / (select count(*) from Some_Table) AS Total_percentage
FROM Some_Table
GROUP BY some_field

Upvotes: 1

CL.
CL.

Reputation: 180070

It is not allowed to access aliases in a SELECT clause from within the same SELECT clause.

However, it is possible to move the entire query with the alias into a subquery:

SELECT some_field,
       The_Total,
       (SELECT The_Total / COUNT(*)
        FROM Some_Table
       ) AS fraction_of_The_Total
FROM (SELECT some_field,
             COUNT(*) AS The_Total
      FROM Some_Table
      GROUP BY some_field)

Upvotes: 1

Related Questions