SQL Server – How to Calculate Sums of Two Columns from Join Queries

join;sql serversum

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

Best Answer

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.

Related Question