CODEWITHSUNDEEP
mysqlsql
da1lbi3
da1lbi3

Reputation: 4519

Calculate on alias name

I have this query

SELECT number_title,
   (SELECT COUNT(*)
    FROM poll_stemmen
    )   AS total_number,

    (SELECT COUNT(ps.number_id)
            FROM poll_stemmen
             group by ps.number_id
            ) AS voted
   FROM poll_numbers AS pn
   RIGHT JOIN poll_stemmen AS ps ON ps.number_id = pn.id
   GROUP BY ps.number_id

But I want to do a calculate between the voted and total_number fields

So I made this query:

SELECT number_title,
   (SELECT COUNT(*)
    FROM poll_stemmen
    )   AS total_number,

    (SELECT COUNT(ps.number_id)
            FROM poll_stemmen
             group by ps.number_id
            ) AS voted, (SELECT voted) AS another_number

         FROM poll_numbers AS pn
         RIGHT JOIN poll_stemmen AS ps ON ps.number_id = pn.id
         GROUP BY ps.number_id

But I get this error:

Reference 'voted' not supported (reference to group function)

How can I fix this?

Upvotes: 1

Views: 34

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can just wrap your query in a sub-query. Then use its aliases any way you like in the outer query, e.g.

SELECT number_title, total_number, voted, 
       total_number * voted AS myCalculatedColumn
FROM (
SELECT number_title,
       (SELECT COUNT(*)
        FROM poll_stemmen) AS total_number,    
       (SELECT COUNT(ps.number_id)
        FROM poll_stemmen
        group by ps.number_id) AS voted
FROM poll_numbers AS pn
RIGHT JOIN poll_stemmen AS ps ON ps.number_id = pn.id
GROUP BY ps.number_id ) t

Upvotes: 1

Related Questions