CODEWITHSUNDEEP
mysqlsql
Aymn Alaney
Aymn Alaney

Reputation: 523

mysql - Dynamic column alias

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

Upvotes: 3

Views: 9567

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

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;

Upvotes: 9

Saravana Kumar
Saravana Kumar

Reputation: 3729

Check whether it works.

Added bracket to column name.

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

Upvotes: -1

Related Questions