CODEWITHSUNDEEP
mysqldatabase
anon
anon

Reputation:

Avoiding redundant expressions in SELECT query

Is there any way to avoid repeating column expressions in the SELECT query? I want to divide the sum and count of a column but would like to use the assigned name instead of repeating SUM(value)/COUNT(value) or using a sub query. Is this possible? If so, does that speed up the query by not repeating the calculation of the sum and count or does mysql remember already calculated expressions?

SELECT datalist.type, SUM(value) AS type_sum, COUNT(value) AS type_count, type_sum/type_count
FROM (...) AS datalist
GROUP BY datalist.type

throws: #1054 - Unknown column 'type_sum' in field list

Upvotes: 0

Views: 83

Answers (2)

stackFan
stackFan

Reputation: 1608

One workaround would be to use a alias table with pre-defined calculations and then later call it from outer table such as: 

select d.type_sum/d.type_count as dividedValue from (SELECT datalist.type, SUM(value)
AS type_sum, COUNT(value) AS type_count
FROM (...) )AS d
GROUP BY d.type

Upvotes: 0

Eric
Eric

Reputation: 3257

Unless you put it in outer query, this is the only way. 

SELECT datalist.type, SUM(value) AS type_sum, COUNT(value) AS type_count, SUM(value)/COUNT(value)
FROM (...) AS datalist
GROUP BY datalist.type

Upvotes: 0

Related Questions