How to use field name returned by subquery inside another subquery-CodePudding

Example:

SELECT COUNT(id) AS total_orders, 
( ( SELECT COUNT(id) FROM orders WHERE status = 'Closed' ) / total_orders * 100) AS percent_closed_orders 
FROM orders

Question: How can I use total_orders in the subquery? I get a an error "unknown column 'total_orders' in 'field list'".

CodePudding user response：

SELECT COUNT(id) AS total_orders, 
       SUM( status = 'Closed' ) / COUNT(id) * 100 AS percent_closed_orders 
FROM orders

If id cannot be NULL (for example it is primary key) then you may use COUNT(*) for to count total rows amount.

CodePudding user response：

You can't use a column alias in the same SELECT statement in which it is defined. You can, however, use the alias in a subsequent SELECT statement, provided that the alias is defined in an outer SELECT statement.

In your example, you would need to move the inner SELECT statement into a subquery in order to be able to use the total_orders alias:

SELECT COUNT(id) AS total_orders, 
( ( SELECT COUNT(id) FROM orders WHERE status = 'Closed' ) / (SELECT total_orders FROM (SELECT COUNT(id) AS total_orders FROM orders) AS x) * 100) AS percent_closed_orders 
FROM orders