Multiply multiple rows and then add a percentage SQL

Question

I am trying to get the total price for a customer invoice. I have intMaterialQuantity * monMaterialCost to give me total cost for material. Then I need to get labor cost by multiplying intHoursWorked * monHourlyRate. I then need those two totals added together and add 35% profit. I need this to show up per job as monCustomerInvoice.

SELECT
     TJ.intJobID
    ,TJS.strJobStatus
    ,SUM ((TJM.intMaterialQuantity * TM.monMaterialCost)+(TJE.intHoursWorked * TE.monHourlyRate))(1.35) AS monCustomerInvoice


FROM 
     TJobs          AS TJ   
    ,TJobMaterials  AS TJM
    ,TMaterials     AS TM
    ,TJobStatus     AS TJS
    ,TJobEmployees  AS TJE
    ,TEmployees     AS TE
WHERE
        TJM.intJobID        = TJ.intJobID
    AND TJM.intMaterialID   = TM.intMaterialID
    AND TJ.intJobStatusID   = TJS.intJobStatusID
    AND TE.intEmployeeID    = TJE.intEmployeeID
    AND TJE.intJobID        = TJ.intJobID
    AND TJ.intJobStatusID = 3
GROUP BY
     TJ.intJobID
    ,TJS.strJobStatus

This is what I am getting

        intJobID    strJobStatus                monCustomerInvoice
   ----------- ------------------------     ------------------------
        1           Complete                    13014.8500
        3           Complete                    2907.9500
        4           Complete                    1146.7800
        8           Complete                    15270.6700
        9           Complete                    10290.6700

This is the output I need

    intJobID    strJobStatus                monCustomerInvoice
   ----------- -----------------         ------------------------
        1           Complete                        3,979.46
        3           Complete                        5,154.33            
        4           Complete                        1,654.33
        8           Complete                        19,284.29
        9           Complete                        12,102.29

I can't figure out why it is giving me such large totals. I know this is the old syntax but it this is how I am required to submit. Thanks!

SAMPLE DATA

 -- --------------------------------------------------------------------------------
 -- Step #1.1: Create Tables
 -- --------------------------------------------------------------------------------
 CREATE TABLE TJobs
(
      intJobID                          INTEGER         NOT NULL
     ,intCustomerID                     INTEGER         NOT NULL
     ,strJobDescription                 VARCHAR(50)     NOT NULL
     ,intJobStatusID                        INTEGER         NOT NULL
     ,dteJobStartDate                   DATETIME        NOT NULL
     ,dteJobFinishDate                  DATETIME        NOT NULL
     ,CONSTRAINT TJobs_PK PRIMARY KEY ( intJobID )
)

CREATE TABLE TJobStatus
(
     intJobStatusID                     INTEGER         NOT NULL
    ,strJobStatus                       VARCHAR(50)     NOT NULL
    ,CONSTRAINT TJobStatus_PK PRIMARY KEY ( intJobStatusID )
)
CREATE TABLE TJobCustomers
(
     intJobID                           INTEGER         NOT NULL
    ,intCustomerID                      INTEGER         NOT NULL
    ,CONSTRAINT TJobCustomers_PK PRIMARY KEY ( intJobID, intCustomerID )
)

CREATE TABLE TCustomers
(
     intCustomerID                      INTEGER         NOT NULL
    ,strFirstName                       VARCHAR(50)     NOT NULL
    ,strLastName                        VARCHAR(50)     NOT NULL
    ,strAddress                         VARCHAR(50)     NOT NULL
    ,strCity                            VARCHAR(50)     NOT NULL
    ,strZipCode                         VARCHAR(50)     NOT NULL
    ,CONSTRAINT TCustomers_PK PRIMARY KEY ( intCustomerID )
)


CREATE TABLE TJobMaterials
(
     intJobID                           INTEGER         NOT NULL
    ,intMaterialID                      INTEGER         NOT NULL
    ,intMaterialQuantity                INTEGER         NOT NULL
    ,CONSTRAINT TJobMaterials_PK PRIMARY KEY ( intJobID, intMaterialID )
)

CREATE TABLE TMaterials
(
     intMaterialID                      INTEGER         NOT NULL
    ,strMaterials                       VARCHAR(50)     NOT NULL
    ,monMaterialCost                    MONEY           NOT NULL
    ,CONSTRAINT TMaterials_PK PRIMARY KEY ( intMaterialID )
)

CREATE TABLE TJobEmployees
(
     intJobID                           INTEGER         NOT NULL
    ,intEmployeeID                      INTEGER         NOT NULL
    ,intHoursWorked                     INTEGER         NOT NULL
    ,CONSTRAINT TJobEmployees_PK PRIMARY KEY ( intJobID, intEmployeeID )
)

CREATE TABLE TEmployees
(
    intEmployeeID                       INTEGER         NOT NULL
   ,strFirstName                        VARCHAR(50)     NOT NULL
   ,strLastName                     VARCHAR(50)     NOT NULL
   ,dteHireDate                     DATETIME        NOT NULL
   ,monHourlyRate                       MONEY           NOT NULL
   ,CONSTRAINT TEmployees_PK PRIMARY KEY ( intEmployeeID )
)   

CREATE TABLE TEmployeeSkills
(
    intEmployeeID                       INTEGER         NOT NULL
   ,intSkillID                          INTEGER         NOT NULL
   ,CONSTRAINT TEmployeeSkills_PK PRIMARY KEY ( intEmployeeID, intSkillID )
)

CREATE TABLE TSkills
(
    intSkillID                          INTEGER         NOT NULL
   ,strSkills                           VARCHAR(50)     NOT NULL
   ,CONSTRAINT TSkills_PK PRIMARY KEY ( intSkillID )
 )


-- --------------------------------------------------------------------------------
-- Step #1.2: Identify and Create Foreign Keys
-- --------------------------------------------------------------------------------
--  ALTER TABLE ChildTable ADD CONSTRAINT ChildTable_ParentTable_FK
--  FOREIGN KEY ( ChildColumns ) REFERENCES ParentTable ( ParentColumns )

-- #    Child                               Parent                      Column(s)
-- -    -----                               ------                      ---- -----
-- 1    TJobCustomers                       TJobs                       intJobID

-- 2    TJobCustomers                       TCustomers                  intCustomerID

-- 3    TJobMaterials                       TJobs                       intJobID

-- 4    TJobMaterials                       TMaterials                  intMaterialID

-- 5    TJobEmployees                       TEmployees                  intEmployeeID

-- 6    TJobEmployees                       TJobs                       intJobID

-- 7    TEmployeeSkills                     TEmployees                  intEmployeeID

-- 8    TEmployeeSkills                     TSkills                     intSkillID

-- 9    TJobStatus                          TJobs                       intJobStatusID

-- 1
ALTER TABLE TJobCustomers ADD CONSTRAINT TJobCustomers_TJobs_FK
FOREIGN KEY ( intJobID ) REFERENCES TJobs ( intJobID )

-- 2
ALTER TABLE TJobCustomers ADD CONSTRAINT TJobCustomers_TCustomers_FK
FOREIGN KEY ( intCustomerID ) REFERENCES TCustomers ( intCustomerID )

 -- 3
 ALTER TABLE TJobMaterials ADD CONSTRAINT TJobMaterials_TJobs_FK
 FOREIGN KEY ( intJobID ) REFERENCES TJobs ( intJobID )

 -- 4
 ALTER TABLE TJobMaterials ADD CONSTRAINT TJobMaterials_TMaterials_FK
 FOREIGN KEY ( intMaterialID ) REFERENCES TMaterials ( intMaterialID )

 -- 5
 ALTER TABLE TJobEmployees ADD CONSTRAINT TJobEmployees_TEmployees_FK
 FOREIGN KEY ( intEmployeeID ) REFERENCES TEmployees ( intEmployeeID )

 -- 6
 ALTER TABLE TJobEmployees ADD CONSTRAINT TJobEmployees_TJobs_FK
 FOREIGN KEY ( intJobID ) REFERENCES TJobs ( intJobID )

 -- 7
 ALTER TABLE TEmployeeSkills ADD CONSTRAINT TEmployeeSkills_TEmployees_FK
 FOREIGN KEY ( intEmployeeID ) REFERENCES TEmployees ( intEmployeeID )

 -- 8
 ALTER TABLE TEmployeeSkills ADD CONSTRAINT TEmployeeSkills_TSkills_FK
 FOREIGN KEY ( intSkillID ) REFERENCES TSkills ( intSkillID )

-- 9
ALTER TABLE TJobs ADD CONSTRAINT TJobs_TJobStatus_FK
FOREIGN KEY ( intJobStatusID ) REFERENCES TJobStatus ( intJobStatusID )
-- --------------------------------------------------------------------------------
-- Step #2.1: Add Data
-- --------------------------------------------------------------------------------
--Inserts into TJobStatus
INSERT INTO TJobStatus( intJobStatusID, strJobStatus)
VALUES  (1, 'Open')

INSERT INTO TJobStatus( intJobStatusID, strJobStatus)
VALUES  (2, 'In Process')

INSERT INTO TJobStatus( intJobStatusID, strJobStatus)
VALUES  (3, 'Complete')


--Inserts into TJobs
INSERT INTO TJobs ( intJobID, intCustomerID, strJobDescription, intJobStatusID, dteJobStartDate, dteJobFinishDate )
VALUES  (1, 1, 'Kitchen Remodel', 3, '10/15/14', '11/30/14' )

 INSERT INTO TJobs ( intJobID, intCustomerID, strJobDescription, intJobStatusID, dteJobStartDate, dteJobFinishDate )
 VALUES (2, 2, 'Bathroom Remodel', 3, '03/10/15', '03/25/15' )

 INSERT INTO TJobs ( intJobID, intCustomerID, strJobDescription, intJobStatusID, dteJobStartDate, dteJobFinishDate )
 VALUES (3, 3, 'Bedroom Remodel', 3, '06/09/15', '06/25/15' )

INSERT INTO TJobs ( intJobID, intCustomerID, strJobDescription, intJobStatusID, dteJobStartDate, dteJobFinishDate )
VALUES  (4, 4, 'Bedroom Remodel', 3, '10/09/15', '10/25/15' )

 INSERT INTO TJobs ( intJobID, intCustomerID, strJobDescription, intJobStatusID, dteJobStartDate, dteJobFinishDate )
 VALUES (5, 2, 'Basement Remodel', 1, '', '' )

INSERT INTO TJobs ( intJobID, intCustomerID, strJobDescription, intJobStatusID, dteJobStartDate, dteJobFinishDate )
VALUES  (6, 3, 'Roof Replacement', 1, '', '' )

INSERT INTO TJobs ( intJobID, intCustomerID, strJobDescription, intJobStatusID, dteJobStartDate, dteJobFinishDate )
VALUES  (7, 3, 'Basement Remodel', 2, '10/10/16', '' )

INSERT INTO TJobs ( intJobID, intCustomerID, strJobDescription, intJobStatusID, dteJobStartDate, dteJobFinishDate )
VALUES  (8, 1, 'Basement Remodel', 3, '06/10/15', '07/20/15' )

INSERT INTO TJobs ( intJobID, intCustomerID, strJobDescription, intJobStatusID, dteJobStartDate, dteJobFinishDate )
VALUES  (9, 1, 'Bathroom Remodel', 3, '03/05/16', '05/15/16' )

INSERT INTO TJobs ( intJobID, intCustomerID, strJobDescription, intJobStatusID, dteJobStartDate, dteJobFinishDate )
VALUES  (10, 1, 'Roof Replacement', 2, '10/01/16', '' )

INSERT INTO TJobs ( intJobID, intCustomerID, strJobDescription, intJobStatusID, dteJobStartDate, dteJobFinishDate )
VALUES  (11, 1, 'Living Room Remodel', 1, '', '' )

--TCustomers
INSERT INTO TCustomers ( intCustomerID, strFirstName, strLastName, strAddress, strCity, strZipCode )
VALUES  ( 1, 'Bob', 'Belcher', '489 Some St.', 'Cincinnati', '42561' )

INSERT INTO TCustomers ( intCustomerID, strFirstName, strLastName, strAddress, strCity, strZipCode )
VALUES  ( 2, 'Tony', 'Stark', '284 Nowhere St.', 'Cincinnati', '42564' )

INSERT INTO TCustomers ( intCustomerID, strFirstName, strLastName, strAddress, strCity, strZipCode )
VALUES  ( 3, 'Peter', 'Parker', '761 Main St.', 'Cincinnati', '42564' )

INSERT INTO TCustomers ( intCustomerID, strFirstName, strLastName, strAddress, strCity, strZipCode )
VALUES  ( 4, 'Hans', 'Solo', '143 Crazy St.', 'Cincinnati', '42564' )

INSERT INTO TCustomers ( intCustomerID, strFirstName, strLastName, strAddress, strCity, strZipCode )
VALUES  ( 5, 'Steve', 'Smith', '8463 Main St.', 'Cincinnati', '42564' )

INSERT INTO TCustomers ( intCustomerID, strFirstName, strLastName, strAddress, strCity, strZipCode )
VALUES  ( 6, 'Chewie', 'Chewbacca', '1864 Main St.', 'Cincinnati', '42564' )

INSERT INTO TCustomers ( intCustomerID, strFirstName, strLastName, strAddress, strCity, strZipCode )
VALUES  ( 7, 'Louis', 'Griffen', '8134 Spooner St.', 'Cincinnati', '42564' )

--Inserts into TJobCustomers
INSERT INTO TJobCustomers( intJobID, intCustomerID )
VALUES  (1, 1 )

INSERT INTO TJobCustomers ( intJobID, intCustomerID )
VALUES  (2, 2 )

INSERT INTO TJobCustomers ( intJobID, intCustomerID )
VALUES  (3, 3 )

INSERT INTO TJobCustomers ( intJobID, intCustomerID )
VALUES  (4, 4 )

INSERT INTO TJobCustomers ( intJobID, intCustomerID )
VALUES  (5, 2 )

INSERT INTO TJobCustomers ( intJobID, intCustomerID )
VALUES  (6, 3 )

INSERT INTO TJobCustomers ( intJobID, intCustomerID )
VALUES  (7, 3 )

INSERT INTO TJobCustomers ( intJobID, intCustomerID )
VALUES  (8, 1 )

INSERT INTO TJobCustomers ( intJobID, intCustomerID )
VALUES  (9, 1 )

INSERT INTO TJobCustomers ( intJobID, intCustomerID )
VALUES  (10, 1 )

INSERT INTO TJobCustomers ( intJobID, intCustomerID )
VALUES  (11, 1 )


--Insert into TMaterials
INSERT INTO TMaterials ( intMaterialID, strMaterials, monMaterialCost )
VALUES  ( 1, 'Nails', '4.99' )

INSERT INTO TMaterials ( intMaterialID, strMaterials, monMaterialCost )
VALUES  ( 2, 'Drywall per 32 sqft', '12.99' )

INSERT INTO TMaterials ( intMaterialID, strMaterials, monMaterialCost )
VALUES  ( 3, '2 x 4', '1.89' )

INSERT INTO TMaterials ( intMaterialID, strMaterials, monMaterialCost )
VALUES  ( 4, 'Paint per gallon', '32.00' )

INSERT INTO TMaterials ( intMaterialID, strMaterials, monMaterialCost )
VALUES  ( 5, 'Tile per sqft', '4.99' )

INSERT INTO TMaterials ( intMaterialID, strMaterials, monMaterialCost )
VALUES  ( 6, 'Copper Water 10ft line', '6.99' )

INSERT INTO TMaterials ( intMaterialID, strMaterials, monMaterialCost )
VALUES  ( 7, 'Screws', '4.99' )

INSERT INTO TMaterials ( intMaterialID, strMaterials, monMaterialCost )
VALUES  ( 8, 'Shingles', '40.99' )

INSERT INTO TMaterials ( intMaterialID, strMaterials, monMaterialCost )
VALUES  ( 9, 'Tar Paper per sqft', '1.99' )

 INSERT INTO TMaterials ( intMaterialID, strMaterials, monMaterialCost )
 VALUES ( 10, 'Elecrictical Wire per ft.', '1.99' )

INSERT INTO TMaterials ( intMaterialID, strMaterials, monMaterialCost )
VALUES  ( 11, 'Caulk', '3.99' )


--Insert into TJobMaterials

--Materials for Job 1 Customer 1
INSERT INTO TJobMaterials ( intJobID, intMaterialID, intMaterialQuantity )
VALUES  (1, 1, 50 )

INSERT INTO TJobMaterials ( intJobID, intMaterialID, intMaterialQuantity )
VALUES  (1, 2, 20 )

INSERT INTO TJobMaterials ( intJobID, intMaterialID, intMaterialQuantity )
VALUES  (1, 3, 20 )

INSERT INTO TJobMaterials ( intJobID, intMaterialID, intMaterialQuantity ) 
VALUES  (1, 4, 5 )

INSERT INTO TJobMaterials ( intJobID, intMaterialID, intMaterialQuantity )
VALUES  (1, 5, 35 )



 --Materials for Job 3 Customer 3
INSERT INTO TJobMaterials ( intJobID, intMaterialID, intMaterialQuantity )
VALUES  (3, 7, 30 )

INSERT INTO TJobMaterials ( intJobID, intMaterialID, intMaterialQuantity )
VALUES  (3, 2, 10 )

INSERT INTO TJobMaterials ( intJobID, intMaterialID, intMaterialQuantity )
VALUES  (3, 4, 2 )

INSERT INTO TJobMaterials ( intJobID, intMaterialID, intMaterialQuantity )
VALUES  (3, 1, 30 )

--Materials for Job 4 Customer 4
INSERT INTO TJobMaterials ( intJobID, intMaterialID, intMaterialQuantity )
 VALUES (4, 7, 65 )

INSERT INTO TJobMaterials ( intJobID, intMaterialID, intMaterialQuantity )
VALUES  (4, 2, 22 )

INSERT INTO TJobMaterials ( intJobID, intMaterialID, intMaterialQuantity )
VALUES  (4, 4, 4 )

INSERT INTO TJobMaterials ( intJobID, intMaterialID, intMaterialQuantity )
VALUES  (4, 1, 50 )

INSERT INTO TJobMaterials ( intJobID, intMaterialID, intMaterialQuantity )
VALUES  (4, 3, 20 )

--Materials for Job 8 Customer 1
INSERT INTO TJobMaterials ( intJobID, intMaterialID, intMaterialQuantity )
VALUES  (8, 3, 33 )

 INSERT INTO TJobMaterials ( intJobID, intMaterialID, intMaterialQuantity )
 VALUES (8, 7, 25 )

INSERT INTO TJobMaterials ( intJobID, intMaterialID, intMaterialQuantity )
VALUES  (8, 1, 15 )

 INSERT INTO TJobMaterials ( intJobID, intMaterialID, intMaterialQuantity )
 VALUES (8, 2, 31 )

INSERT INTO TJobMaterials ( intJobID, intMaterialID, intMaterialQuantity )
VALUES  (8, 4, 10 )

--Materials for Job 9 Customer 1
 INSERT INTO TJobMaterials ( intJobID, intMaterialID, intMaterialQuantity )
  VALUES    (9, 3, 33 )

 INSERT INTO TJobMaterials ( intJobID, intMaterialID, intMaterialQuantity )
 VALUES (9, 7, 25 )

INSERT INTO TJobMaterials ( intJobID, intMaterialID, intMaterialQuantity )
VALUES  (9, 1, 15 )

INSERT INTO TJobMaterials ( intJobID, intMaterialID, intMaterialQuantity )
VALUES  (9, 2, 31 )

INSERT INTO TJobMaterials ( intJobID, intMaterialID, intMaterialQuantity )
VALUES  (9, 4, 10 )



 --Insert Employees into TEmployees

 --Employee 1
 INSERT INTO TEmployees ( intEmployeeID, strFirstName, strLastName, dteHireDate, monHourlyRate )
 VALUES ( 1, 'Stan', 'Smith', '02/10/2013', '18.00' )

 --Employee 2
 INSERT INTO TEmployees ( intEmployeeID, strFirstName, strLastName, dteHireDate, monHourlyRate )
 VALUES ( 2, 'Glen', 'Quagmire', '06/10/2012', '22.00' )

--Employee 3
INSERT INTO TEmployees ( intEmployeeID, strFirstName, strLastName, dteHireDate, monHourlyRate )
VALUES  ( 3, 'Roger', 'Smith', '011/22/2016', '14.00' )

--Employee 4
INSERT INTO TEmployees ( intEmployeeID, strFirstName, strLastName, dteHireDate, monHourlyRate )
VALUES  ( 4, 'Luke', 'Cage', '01/10/2012', '23.00' )

  --Employee 5
  INSERT INTO TEmployees ( intEmployeeID, strFirstName, strLastName, dteHireDate, monHourlyRate )
 VALUES ( 5, 'Bill', 'Smith', '10/10/2016', '12.00' )

 --Insert Employees into TJobEmployees
 --Job 1 Employee 2
 INSERT INTO TJobEmployees ( intJobID, intEmployeeID, intHoursWorked )
 VALUES ( 1, 2, 50 )

  --Job 1 Employee 4
  INSERT INTO TJobEmployees ( intJobID, intEmployeeID, intHoursWorked )
  VALUES    ( 1, 4, 42 )

 --Job 2 Employee 1
 INSERT INTO TJobEmployees ( intJobID, intEmployeeID, intHoursWorked )
 VALUES ( 2, 1, 15 )

 --Job 3 Employee 3
INSERT INTO TJobEmployees ( intJobID, intEmployeeID, intHoursWorked )
 VALUES ( 3, 3, 30 )

--Job 3 Employee 5
INSERT INTO TJobEmployees ( intJobID, intEmployeeID, intHoursWorked )
 VALUES ( 3, 5, 5 )

 --Job 4 Employee 5
INSERT INTO TJobEmployees ( intJobID, intEmployeeID, intHoursWorked )
VALUES  ( 4, 5, 2 )

 --Job 5 Employee 4
INSERT INTO TJobEmployees ( intJobID, intEmployeeID, intHoursWorked )
VALUES  ( 5, 4, 0 )

--Job 5 Employee 1
INSERT INTO TJobEmployees ( intJobID, intEmployeeID, intHoursWorked )
VALUES  ( 5, 1, 0 )



 --Job 8 Employee 2
 INSERT INTO TJobEmployees ( intJobID, intEmployeeID, intHoursWorked )
 VALUES ( 8, 2, 70 )

 --Job 8 Employee 3
 INSERT INTO TJobEmployees ( intJobID, intEmployeeID, intHoursWorked )
 VALUES ( 8, 3, 70 )

--Job 9 Employee 1
 INSERT INTO TJobEmployees ( intJobID, intEmployeeID, intHoursWorked )
VALUES  ( 9, 1, 82 )

 --Job 9 Employee 5
 INSERT INTO TJobEmployees ( intJobID, intEmployeeID, intHoursWorked )
 VALUES ( 9, 5, 2 )

 --Job 10 Employee 4
INSERT INTO TJobEmployees ( intJobID, intEmployeeID, intHoursWorked )
VALUES  ( 10, 4, 25 )

Use standard, proper, modern, explicit `JOIN` syntax. *Never* use commas in the `FROM` clause. — Gordon Linoff, Dec 14 '16 at 20:11
I suggest you remove the `group by` and the `sum` from the column list. Then add all the fields required in the formula in the column list sorted by `intJobID`. Copy data to Excel and do your sums and grouping to verify. — domenicr, Dec 14 '16 at 20:13
@GordonLinoff he mentioned that he is required to use old syntax. — domenicr, Dec 14 '16 at 20:14
I take it this is homework? Too bad your professor is requiring you to learn poor coding practices. Are you forced to do this in Win95 too? Once again....without some sample data we are left guessing as to what this is doing. — Sean Lange, Dec 14 '16 at 20:27
@SeanLange Yes this is very frustrating since I am taking this class online. I just can't seem to find references online to do it in the old syntax. That is why I am struggling with this. I would prefer the new syntax. — , Dec 14 '16 at 21:03

score 1 · Accepted Answer · edited May 23 '17 at 11:52

update

That question update to include the schema and data was perfect, helps so much.

Instead of having a bushy query to try and get your two calculations at once, you should try to use two subqueries, one for the material cost for each job id, the other for the labor for each job id.

Like yesterday, you have an issue with this query producing duplicates because of how these tables are related. You have to find where your duplicates are coming from and how other rows are being erroneously filtered out.

Look at the table schema and figure out what columns are unique keys, which others require another way to get to a 1:1 result.

Multiply multiple rows and then add a percentage SQL

1 Answers1