0

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

  • Thanks for you were giving me the idea, but it was so complex by the null values! I just re-posted the question clear as I can, Is there any simple way to do this? I greatly appreciate your helps @Scott Hodgin – Aaby Jun 24 at 19:25
  • If you want to display the total cost as a seperate column you can use a windows function (they are like group by but they show all rows in the group). What columns do you want in your final output though? – Jhunter1 Jun 24 at 20:17
3

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.

  • Yes It works, Great work.. Thank you so much @Kirk Saunders – Aaby Jun 25 at 4:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.