Output alias in MySQL SELECT query

I'm trying to create a query. The query is part of a work-planning system, and is used to find out how many hours each user of the system is allocated per day, on all jobs except for the one being currently viewed.

I want the output to have nicely named Aliases, such as hours_mon, hours_tue etc.

Currently the query is as follows:

SELECT SUM(alloc.hours), alloc.day FROM grwp_allocations AS alloc LEFT JOIN grwp_jobs AS job ON (job.id = alloc.job_id AND job.id != '3') WHERE alloc.user_id = '35' AND alloc.deleted = '0' AND alloc.acknowledged = '0' GROUP BY alloc.day

Which works fine, but I really want to replace 'SUM(alloc.hours)' with hours_...

I've tried the following but it throws up and error

SELECT SUM(alloc.hours) AS CONCAT('hours_',alloc.day), alloc.day

Table schema: grwp_allocations:

• id
• job_id
• user_id
• day
• hours

grwp_jobs:

• id

Remember, I want data to be available as the following at the end:

hours_mon, hours_tue, hours_wed etc.. where 'mon','tue','wed' etc are the valu开发者_如何转开发es from the DAY field and the value of the alias is the (summed) hours.

Thank you.

This doesn't make a lot of sense.

You EITHER want an hours_per_day column, which will generate data like:

hours_per_day  |  day
---------------+-------
10             |  mon
9              |  tue

this is obviously an easy fix, just renaming the SUM column in your query:

SELECT SUM(alloc.hours) AS hours_per_day`

OR

you want a column per day of the week (hours_mon,hours_tue...) which will require a completely different query - not just different column names! This case is called a PIVOT table, and will look like this:

user | mon | tue | wed
-----+-----+-----+----
 35  | 10  |  9  |  0

For instructions on how to achieve this in MySQL go to: http://www.artfulsoftware.com/infotree/queries.php?&bw=1339#78

One of the options they show there would be something like:

SELECT alloc.user_id AS user,
       SUM(IF(alloc.day = 'mon',alloc.hours,0)) as mon,
       SUM(IF(alloc.day = 'tue',alloc.hours,0)) as tue,
       SUM(IF(alloc.day = 'wed',alloc.hours,0)) as wed,
       SUM(IF(alloc.day = 'thu',alloc.hours,0)) as thu,
       SUM(IF(alloc.day = 'fri',alloc.hours,0)) as fri
FROM grwp_allocations AS alloc
LEFT JOIN grwp_jobs AS job ON (job.id = alloc.job_id AND job.id != '3')
WHERE alloc.user_id = '35'
AND alloc.deleted = '0'
AND alloc.acknowledged = '0'
GROUP BY alloc.user_id