How do I make sql timestamp as column name?

Question

For instance:

select count(*) as (select date_sub(curdate(),interval 4 day)) 
from userinfo 
where createTime > (select date_sub(curdate(),interval 4 day));

This is not working. It says the syntax after 'as' is not correct. How do I make this work?

I want the result to be like this:

| |2016-01-14|
|-|----------|
|1|   1000   |

Answer 1

With normal static query you cannot define column name as variable/subquery, but you could achieve it with dynamic SQL(prepared statements):

SET @sql =
    CONCAT('select count(*) as `',(select date_sub(curdate(),interval 4 day)),'` from userinfo where createTime > (select date_sub(curdate(),interval 4 day));');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SqlFiddleDemo

Output:

╔════════════╗
║ 2016-01-14 ║
╠════════════╣
║          2 ║
╚════════════╝