MySQL Views: Referencing one calculated field (by name) in another calculated field

Question

How can I define a view that has two calculated fields, for instance...

 ('TableName'.'BlueSquares' + 'TableName'.'RedSquares') AS TotalSquares, ('TableName'.'BlueCirles' + 'TableName'.'RedCircles') AS TotalCircles

... and create a third calculated field that's based on the first two calculated fields, as in...

 ('ViewName'.'TotalSquares' + 'ViewName'.'TotalCircles') AS TotalShapes

...?

When I reference the first two calculated fields by name, I get a message that the fields are unknown.

Thanks!

Answer 1

Since subqueries are not allowed in views, you will need to simulate them by creating multiple views.

For example, this query will solve your issue if directly executed:

SELECT 
    TotalCircles + TotalSquares AS TotalShapes
FROM
    (SELECT 
        BlueCirles + RedCircles AS TotalCircles,
        BlueSquares + RedSquares AS TotalSquares
    FROM
        (SELECT
            2 AS BlueCirles,
            3 AS RedCircles,
            4 AS BlueSquares,
            5 AS RedSquares
        ) AS shapes
    ) as totals;

According to the MySQL documentation views have the restriction on not being able to contain subqueries in the FROM clause. To work around this limitation and turn this query into a view, break it up into 3 views (one for each subquery) with the last giving the desired combination of fields:

CREATE VIEW shapes AS
SELECT
    2 AS BlueCirles,
    3 AS RedCircles,
    4 AS BlueSquares,
    5 AS RedSquares;

CREATE VIEW totals AS
SELECT 
    BlueCirles + RedCircles AS TotalCircles,
    BlueSquares + RedSquares AS TotalSquares
FROM
    shapes;

CREATE VIEW result AS
SELECT 
    TotalCircles + TotalSquares AS TotalShapes
FROM
    totals;

SELECT * FROM result;