Dynamic alias 2

[mcowen]

Hi

I'm trying to output the previous 12 months and have the month name as the column heading. It doesnt want to let me use a function as the alias.

SELECT Department, 
SUM(case when Month=MONTH((CURDATE() - INTERVAL 11 MONTH)) then Total else '0' end) AS DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%M-%Y'),

Does anyone have any ideas how I can do this please?

Thanks

Matt

 

[TonyGroves]

Are you trying to do something like this?:
[tt]
SELECT
Department,
FORMAT(month,'%M%Y') m,
SUM(
case
when Month=MONTH((CURDATE() - INTERVAL 11 MONTH))
then Total
else 0
end
)
FROM tblname
GROUP BY month
[/tt]

 

[TonyGroves]

Sorry, that should have been "GROUP BY department,month".

 

[mcowen]

Yes thats right but I'm also trying to get the alias to be the month i.e. a table with August 2005 to July 2006 in the column headings. It throws an error when I try and use the

AS DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%M-%Y')



Matt

 

[sleipnir214]

An alias must be a string constant.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[TonyGroves]

mcowen:
The only way you can achieve that is to assemble your query using program code, so that the alias is already defined before passing the query on to MySQL.