Page 1 of 1

Using aliases to do math

Posted: Sun May 31, 2009 9:14 am
by cone13cone
I want to be able to use aliases to do math with this query
I would like to do something like this:
(sumHelpHours + totalMaterials + totalHoursRated) AS grandTotal

Any help on how to do this or clean up the query in general would be appreciated.

Code: Select all

 
SELECT jobs.JID AS JID, jobs.CID AS CID, jobs.PID, jobs.ZID, jobs.name, jobs.address, jobs.home, jobs.cell, jobs.work, jobs.summary, jobs.description, jobs.date, invoices.visible, invoices.hours, invoices.helpers, invoices.materials, properties.name AS pname, zones.name AS zname, users.rate, approved.addAmt, approved.subtractAmt, COUNT( invoices.CID ) AS countCID, SUM( invoices.hours ) AS totalHours, (
SUM( invoices.hours )
) *65 AS totalHoursRated, SUM( invoices.materials ) AS totalMaterials, (
SUM( invoices.materials )
) + ( (
SUM( invoices.hours ) ) *65
) AS subTotal,
SUM((invoices.helpers) * (invoices.hours)) AS sumHelpHours
FROM jobs
LEFT JOIN (
invoices, properties, zones, users, approved
) ON ( jobs.PID = properties.PID
AND jobs.ZID = zones.ZID
AND jobs.JID = invoices.JID
AND jobs.CID = invoices.CID
AND jobs.CID = users.CID
AND jobs.JID = approved.JID)
WHERE jobs.JID
IN (
SELECT approved.JID
FROM approved
)
GROUP BY approved.JID