# How to calculate the sums of two columns, when that two columns brought by join queries?

Posted on

### Question :

I ran two queries as shown bellow;

query 1: queried with JobRequiredProducts table
and Product table to list the jobId 1 required all product and unit prices

``````SELECT  j.jobId,
j.productId,
p.productUnitPrice
FROM    JobRequiredProducts J
JOIN    Product p
ON    p.productId = j.productId
WHERE   j.jobId = 1
ORDER BY j.jobId
``````

query 2: queried with JobRequiredLabours table
and Labour table to list the jobId 1 required labours and worked hours

``````SELECT  j.jobId,
j.labourId,
j.hoursOfWork
FROM    JobRequiredLabours J
JOIN    Labour L
ON    l.labourId = j.labourId
WHERE   j.jobId = 1
ORDER BY j.jobId
``````

And I got bellowing results:

``````Query result:

jobId | productId | productUnitPrice
__________________________
1   |     4     | 1500
1   |     5     | 800
1   |     6     | 1200

jobId | labourId | hoursOfWork
______________________________
1   |     1     | 6
1   |     2     | 8
``````

Now I want to calculate the total job cost by total (productUnitPrices) + (hoursOfWork * 30). And display the total cost separately. Please help me to do it. Note: 30 added to multiply because of hourly rate is 30

What you are primarily looking for is a `SUM` command run on your queries where you will `GROUP BY jobId`. That will allow you to get the `SUM` of your hours worked as well as the cost of the parts.

I can see two options that you can utilize. Now there may be some variables I am missing but this should give you a framework to work with.

Basically what you need to do is get the `SUM` separated out which you should and then the math is fairly easy after that. You are able to get `SUM` value by using the `GROUP BY` clause in a `SELECT` query.

Sub Queries As “Tables”

With this approach you create a sort of “table” which has your `SUM` pre calculated which is then added in the main `SELECT`. (I am assuming you have some sort of a `JOB` table which is where the `jobId` column comes from).

``````SELECT J.JobID,
A.SumOfProductUnitPrice + (B.SumOfHoursWorked * 30)
FROM Job J
LEFT OUTER JOIN
(
SELECT  j.jobId,
SUM(p.productUnitPrice) AS SumOfProductUnitPrice --This becomes the name of this "column" in the main SELECT statement
FROM    JobRequiredProducts J
JOIN    Product p
ON    p.productId = j.productId
GROUP BY J.JobID
) A
ON A.JobID = J.JobID
LEFT OUTER JOIN
(
SELECT  j.jobId,
SUM(j.hoursOfWork) AS SumOfHoursWorked --This becomes the name of this "column" in the main SELECT statement
FROM    JobRequiredLabours J
JOIN    Labour L
ON    l.labourId = j.labourId
GROUP BY j.jobId
) B
ON B.JobID = J.JobID
WHERE J.JobID = 1 --WHERE Command can change to return the same information for a different jobID
``````

CTE’s (Common Table Expression)

An alternative that also works is using a `CTE` (a Common Table Expression) which is almost like a `VIEW` that exists only for this query. It allows you to pre-define a query and utilize it later in a different command. In this particular instance we are using it to pre-generate the `SUM` so we can then use it later in a main `SELECT`. The benefit of this is the logic of how the CTE’s are created is not in the main `SELECT` which helps with readability.

``````--All CTE's need to be precided with a ";" if it is not the first statement in the batch
;
WITH ProductSum (JobID, SumOfProductUnitPrice)
AS
(
SELECT  j.jobId,
SUM(p.productUnitPrice) AS SumOfProductUnitPrice
FROM    JobRequiredProducts J
JOIN    Product p
ON    p.productId = j.productId
GROUP BY J.JobID
), --Comma seaparates the two CTE's that could be used in the next statement
HoursWorkSum (JobID, SumOfHoursWorked)
(
SELECT  j.jobId,
SUM(j.hoursOfWork) AS SumOfHoursWorked
FROM    JobRequiredLabours J
JOIN    Labour L
ON    l.labourId = j.labourId
GROUP BY j.jobId
) --No comma follows the last CTE in the chain

SELECT J.JobID,
A.SumOfProductUnitPrice + (B.SumOfHoursWorked * 30)
FROM Job J
LEFT OUTER JOIN ProductSum A
ON A.JobID = J.JobID
LEFT OUTER JOIN HoursWorkSum B
ON B.JobID = J.JobID
WHERE J.JobID = 1 --WHERE command can change to return the same data for a different jobId
``````

These queries will get slightly more complicated, if a job requires more than one instance of a single part AND if that is held through some kind of `QUANTITY` `COLUMN` or if the Hourly Rate for a given person is not 30 every time. But hopefully this will give you something to work with as a start.