Question 113178 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to use field name returned by subquery inside another subquery

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’".

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0 
    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search