Using aliases to do math

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
cone13cone
Forum Newbie
Posts: 24
Joined: Fri Mar 20, 2009 8:32 pm

Using aliases to do math

Post 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
 
Post Reply