user1071461 user1071461 - 5 months ago 11
SQL Question

Select a custom column in MySQL

I want to display a column which calculates two custom columns.

It look something like this

SELECT to_account as account,
SUM(amount) total_claimed,
COUNT(*) as transaction_count,
((SELECT time FROM transactions WHERE to_account = account ORDER BY time DESC LIMIT 1)
- (SELECT time FROM transactions WHERE to_account = account LIMIT 1)) / 3600 as interval_hours,
(transaction_count / interval_hours) as avg_per_hour
FROM transactions
WHERE type='CLAIM' group by to_account ORDER BY COUNT(*)


I get the message "Unknown column 'thetime' in field list"

How can I work with a custom column?

Answer

You should do that using the column itself and not the alias since it's not accessible like

SELECT COUNT(*) as amount, 
`time` as thetime, 
(`time` / amount) as average 
FROM table WHERE..

(OR) get it done in a outer query like

SELECT *, (thetime / amount) as average
FROM (
SELECT COUNT(*) as amount, 
(SELECT time FROM table ...) as thetime
 FROM table WHERE...)XXX;

Per your edited post, either you use the same expression again (OR) get the custom column in a outer query like

SELECT *, (transaction_count / interval_hours) as avg_per_hour
FROM (
SELECT to_account as account, 
SUM(amount) total_claimed, 
COUNT(*) as transaction_count, 
((SELECT time FROM transactions WHERE to_account = account ORDER BY `time` DESC LIMIT 1) 
- (SELECT `time` FROM transactions WHERE to_account = account LIMIT 1)) / 3600 as interval_hours
FROM transactions 
WHERE type='CLAIM' 
group by to_account 
ORDER BY COUNT(*)
) tbl
Comments