sql - mysql - Dynamic column alias

Question

Hi I have "sql query" to see number of the job done for each member in the last week so I create "sql query" and it is work very will but I want to change the alise from week1 or week2 to the data of this week my "sql query" is :

SELECT `staffID`,  
 SUM(if (created_at BETWEEN NOW()-INTERVAL 1 WEEK AND NOW(), 1,0)) 
 AS `week1`, 
 SUM(if (created_at BETWEEN NOW()-INTERVAL 2 WEEK AND NOW()-INTERVAL 1 WEEK, 1,0)) 
 AS `week2`, 
FROM tasks 
WHERE status ='done'
GROUP BY `staffID

and I want to become like this "sql query",but mysql give me error. So, is there anyway to solve the problem?

SELECT `staffID`,  
 SUM(if (created_at BETWEEN NOW()-INTERVAL 1 WEEK AND NOW(), 1,0)) 
 AS NOW()-INTERVAL 1 WEEK, 
 SUM(if (created_at BETWEEN NOW()-INTERVAL 2 WEEK AND NOW()-INTERVAL 1 WEEK, 1,0)) 
 AS NOW()-INTERVAL 2 WEEK, 
FROM tasks 
WHERE status ='done'
GROUP BY `staffID

sql give me this message:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOW()-INTERVAL 1 WEEK

Answer 1

The answer is simple: It is not possible in SQL. Column aliases are constants. You would have to create the query dynamically to achieve what you want:

SET @column_alias1 := NOW() - INTERVAL 1 WEEK;
SET @column_alias2 := NOW() - INTERVAL 2 WEEK;
SET @query := CONCAT('SELECT SUM(...) AS `', @column_alias1, '`, SUM(...) AS `', @column_alias2, '` FROM ...');
PREPARE dynamic_statement FROM @query;
EXECUTE dynamic_statement;