Using aliases to do math
Posted: Sun May 31, 2009 9:14 am
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.
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