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