How to add total of column together
up vote
1
down vote
favorite
I have multiplied TJM.intMaterialQuantity * TM.monMaterialCost That gives me the total of each material item in separate rows. I need those 4 totals added up and placed into a new column called monTotalMaterialCost. The way it is now shows each material in monTotalMaterialCost. How do I get the total of all materials into one row so I can see the job and customer and total cost in one row?
SELECT
TJ.intJobID
,TC.intCustomerID
,TC.strLastName + ', ' + TC.strFirstName AS strCustomerName
,(SUM (TJM.intMaterialQuantity) * SUM (TM.monMaterialCost)) AS monTotalMaterialCost
FROM
TJobs AS TJ
,TJobCustomers AS TJC
,TCustomers AS TC
,TJobMaterials AS TJM
,TMaterials AS TM
WHERE
TJ.intJobID = TJC.intJobID
AND TJC.intCustomerID = TC.intCustomerID
AND TJM.intMaterialID = TM.intMaterialID
AND TJM.intJobID = TJ.intJobID
AND TJ.intJobID = 1
GROUP BY
TJ.intJobID
,TC.intCustomerID
,TC.strLastName + ', ' + TC.strFirstName
,TJM.intMaterialQuantity
,TM.monMaterialCost
SAMPLE INPUT
--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 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 )
/*
Output I'm Getting
intJobID strJobDescription intJobStatusID strJobStatus monTotalMaterialCost
1 Kitchen Remodel 3 Complete 160.00
1 Kitchen Remodel 3 Complete 37.80
1 Kitchen Remodel 3 Complete 259.80
1 Kitchen Remodel 3 Complete 174.65
1 Kitchen Remodel 3 Complete 249.50
Output Wanted
intJobID strJobDescription intJobStatusID strJobStatus monTotalMaterialCost
1 Kitchen Remodel 3 Complete 881.75
I need the total of all 5 outputs into one final total into monTotalMaterialCost
*/
The needed output:
intJobID = 1 strJobDescription = Kitchen Remodel strJobStatus = Complete monTotalMaterialCost = 881.75
What I'm Getting:
intJobID = 1 strJobDescription = Kitchen Remodel strJobStatus = Complete monTotalMaterialCost = 7391.80
What is happening: I have 5 different Materials from TJM.intMaterialQuantity. Each value from each row is 50, 20, 20, 5, 35 Then I have 5 different Cost for each material from TM.monMaterialCost those values matching in same order 4.99,12.99, 1.89, 32.00, 4.99. I am getting the 50+20+20+5+35 = 130 and 4.99+12.99+1.89+32.00+4.99 = 56.86 So I am getting in the final equation 130 * 56.86 = 7,391.80.
What I need is 50 * 4.99 = 249.50, 20 * 12.99 = 259.80, 20 * 1.89 = 37.80, 5 * 32.00 = 160, 35 * 4.99 = 174.65 The I need to add all of those totals together for a total amount spent on materials for the job for a total of 881.75.
I hope this helps better understand my question.
I have figured it out. I have posted correct answer for future questions:
SELECT
TJ.intJobID
,TJ.strJobDescription
,TJ.intJobStatusID
,TJS.strJobStatus
, SUM (TJM.intMaterialQuantity * TM.monMaterialCost) AS monTotalMaterialCost
FROM TJobs AS TJ
join TJobMaterials AS TJM on TJM.intJobID = TJ.intJobID
join TMaterials AS TM on TJM.intMaterialID = TM.intMaterialID
join TJobStatus AS TJS on TJ.intJobStatusID = TJS.intJobStatusID
GROUP BY
TJ.intJobID
,TJ.strJobDescription
,TJ.intJobStatusID
,TJS.strJobStatus
sql
sql-server asked Dec 12 '16 at 17:49
Brad Wethington
426
|
show 2 more comments
up vote
1
down vote
favorite
I have multiplied TJM.intMaterialQuantity * TM.monMaterialCost That gives me the total of each material item in separate rows. I need those 4 totals added up and placed into a new column called monTotalMaterialCost. The way it is now shows each material in monTotalMaterialCost. How do I get the total of all materials into one row so I can see the job and customer and total cost in one row?
SELECT
TJ.intJobID
,TC.intCustomerID
,TC.strLastName + ', ' + TC.strFirstName AS strCustomerName
,(SUM (TJM.intMaterialQuantity) * SUM (TM.monMaterialCost)) AS monTotalMaterialCost
FROM
TJobs AS TJ
,TJobCustomers AS TJC
,TCustomers AS TC
,TJobMaterials AS TJM
,TMaterials AS TM
WHERE
TJ.intJobID = TJC.intJobID
AND TJC.intCustomerID = TC.intCustomerID
AND TJM.intMaterialID = TM.intMaterialID
AND TJM.intJobID = TJ.intJobID
AND TJ.intJobID = 1
GROUP BY
TJ.intJobID
,TC.intCustomerID
,TC.strLastName + ', ' + TC.strFirstName
,TJM.intMaterialQuantity
,TM.monMaterialCost
SAMPLE INPUT
--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 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 )
/*
Output I'm Getting
intJobID strJobDescription intJobStatusID strJobStatus monTotalMaterialCost
1 Kitchen Remodel 3 Complete 160.00
1 Kitchen Remodel 3 Complete 37.80
1 Kitchen Remodel 3 Complete 259.80
1 Kitchen Remodel 3 Complete 174.65
1 Kitchen Remodel 3 Complete 249.50
Output Wanted
intJobID strJobDescription intJobStatusID strJobStatus monTotalMaterialCost
1 Kitchen Remodel 3 Complete 881.75
I need the total of all 5 outputs into one final total into monTotalMaterialCost
*/
The needed output:
intJobID = 1 strJobDescription = Kitchen Remodel strJobStatus = Complete monTotalMaterialCost = 881.75
What I'm Getting:
intJobID = 1 strJobDescription = Kitchen Remodel strJobStatus = Complete monTotalMaterialCost = 7391.80
What is happening: I have 5 different Materials from TJM.intMaterialQuantity. Each value from each row is 50, 20, 20, 5, 35 Then I have 5 different Cost for each material from TM.monMaterialCost those values matching in same order 4.99,12.99, 1.89, 32.00, 4.99. I am getting the 50+20+20+5+35 = 130 and 4.99+12.99+1.89+32.00+4.99 = 56.86 So I am getting in the final equation 130 * 56.86 = 7,391.80.
What I need is 50 * 4.99 = 249.50, 20 * 12.99 = 259.80, 20 * 1.89 = 37.80, 5 * 32.00 = 160, 35 * 4.99 = 174.65 The I need to add all of those totals together for a total amount spent on materials for the job for a total of 881.75.
I hope this helps better understand my question.
I have figured it out. I have posted correct answer for future questions:
SELECT
TJ.intJobID
,TJ.strJobDescription
,TJ.intJobStatusID
,TJS.strJobStatus
, SUM (TJM.intMaterialQuantity * TM.monMaterialCost) AS monTotalMaterialCost
FROM TJobs AS TJ
join TJobMaterials AS TJM on TJM.intJobID = TJ.intJobID
join TMaterials AS TM on TJM.intMaterialID = TM.intMaterialID
join TJobStatus AS TJS on TJ.intJobStatusID = TJS.intJobStatusID
GROUP BY
TJ.intJobID
,TJ.strJobDescription
,TJ.intJobStatusID
,TJS.strJobStatus
sql
sql-server asked Dec 12 '16 at 17:49
Brad Wethington
426
1
If you can give a sample input and a sample output it would be easier to help you
– gh9
Dec 12 '16 at 17:50
3
Just delete columns TJM.intMaterialQuantity, TM.monMaterialCost from GROUP BY clause
– Igor Borisenko
Dec 12 '16 at 18:03
@IgorBorisenko that doesn't work. I get 73910.80. The total should be 3,138.75
– Brad Wethington
Dec 13 '16 at 0:02
@TonyDong I'm not sure what that means.
– Brad Wethington
Dec 13 '16 at 0:02
@gh9 I have added the inputs for the materials and the materials used in job 1.
– Brad Wethington
Dec 13 '16 at 0:08
|
show 2 more comments
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I have multiplied TJM.intMaterialQuantity * TM.monMaterialCost That gives me the total of each material item in separate rows. I need those 4 totals added up and placed into a new column called monTotalMaterialCost. The way it is now shows each material in monTotalMaterialCost. How do I get the total of all materials into one row so I can see the job and customer and total cost in one row?
SELECT
TJ.intJobID
,TC.intCustomerID
,TC.strLastName + ', ' + TC.strFirstName AS strCustomerName
,(SUM (TJM.intMaterialQuantity) * SUM (TM.monMaterialCost)) AS monTotalMaterialCost
FROM
TJobs AS TJ
,TJobCustomers AS TJC
,TCustomers AS TC
,TJobMaterials AS TJM
,TMaterials AS TM
WHERE
TJ.intJobID = TJC.intJobID
AND TJC.intCustomerID = TC.intCustomerID
AND TJM.intMaterialID = TM.intMaterialID
AND TJM.intJobID = TJ.intJobID
AND TJ.intJobID = 1
GROUP BY
TJ.intJobID
,TC.intCustomerID
,TC.strLastName + ', ' + TC.strFirstName
,TJM.intMaterialQuantity
,TM.monMaterialCost
SAMPLE INPUT
--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 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 )
/*
Output I'm Getting
intJobID strJobDescription intJobStatusID strJobStatus monTotalMaterialCost
1 Kitchen Remodel 3 Complete 160.00
1 Kitchen Remodel 3 Complete 37.80
1 Kitchen Remodel 3 Complete 259.80
1 Kitchen Remodel 3 Complete 174.65
1 Kitchen Remodel 3 Complete 249.50
Output Wanted
intJobID strJobDescription intJobStatusID strJobStatus monTotalMaterialCost
1 Kitchen Remodel 3 Complete 881.75
I need the total of all 5 outputs into one final total into monTotalMaterialCost
*/
The needed output:
intJobID = 1 strJobDescription = Kitchen Remodel strJobStatus = Complete monTotalMaterialCost = 881.75
What I'm Getting:
intJobID = 1 strJobDescription = Kitchen Remodel strJobStatus = Complete monTotalMaterialCost = 7391.80
What is happening: I have 5 different Materials from TJM.intMaterialQuantity. Each value from each row is 50, 20, 20, 5, 35 Then I have 5 different Cost for each material from TM.monMaterialCost those values matching in same order 4.99,12.99, 1.89, 32.00, 4.99. I am getting the 50+20+20+5+35 = 130 and 4.99+12.99+1.89+32.00+4.99 = 56.86 So I am getting in the final equation 130 * 56.86 = 7,391.80.
What I need is 50 * 4.99 = 249.50, 20 * 12.99 = 259.80, 20 * 1.89 = 37.80, 5 * 32.00 = 160, 35 * 4.99 = 174.65 The I need to add all of those totals together for a total amount spent on materials for the job for a total of 881.75.
I hope this helps better understand my question.
I have figured it out. I have posted correct answer for future questions:
SELECT
TJ.intJobID
,TJ.strJobDescription
,TJ.intJobStatusID
,TJS.strJobStatus
, SUM (TJM.intMaterialQuantity * TM.monMaterialCost) AS monTotalMaterialCost
FROM TJobs AS TJ
join TJobMaterials AS TJM on TJM.intJobID = TJ.intJobID
join TMaterials AS TM on TJM.intMaterialID = TM.intMaterialID
join TJobStatus AS TJS on TJ.intJobStatusID = TJS.intJobStatusID
GROUP BY
TJ.intJobID
,TJ.strJobDescription
,TJ.intJobStatusID
,TJS.strJobStatus
sql
sql-server asked Dec 12 '16 at 17:49
Brad Wethington
426
I have multiplied TJM.intMaterialQuantity * TM.monMaterialCost That gives me the total of each material item in separate rows. I need those 4 totals added up and placed into a new column called monTotalMaterialCost. The way it is now shows each material in monTotalMaterialCost. How do I get the total of all materials into one row so I can see the job and customer and total cost in one row?
SELECT
TJ.intJobID
,TC.intCustomerID
,TC.strLastName + ', ' + TC.strFirstName AS strCustomerName
,(SUM (TJM.intMaterialQuantity) * SUM (TM.monMaterialCost)) AS monTotalMaterialCost
FROM
TJobs AS TJ
,TJobCustomers AS TJC
,TCustomers AS TC
,TJobMaterials AS TJM
,TMaterials AS TM
WHERE
TJ.intJobID = TJC.intJobID
AND TJC.intCustomerID = TC.intCustomerID
AND TJM.intMaterialID = TM.intMaterialID
AND TJM.intJobID = TJ.intJobID
AND TJ.intJobID = 1
GROUP BY
TJ.intJobID
,TC.intCustomerID
,TC.strLastName + ', ' + TC.strFirstName
,TJM.intMaterialQuantity
,TM.monMaterialCost
SAMPLE INPUT
--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 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 )
/*
Output I'm Getting
intJobID strJobDescription intJobStatusID strJobStatus monTotalMaterialCost
1 Kitchen Remodel 3 Complete 160.00
1 Kitchen Remodel 3 Complete 37.80
1 Kitchen Remodel 3 Complete 259.80
1 Kitchen Remodel 3 Complete 174.65
1 Kitchen Remodel 3 Complete 249.50
Output Wanted
intJobID strJobDescription intJobStatusID strJobStatus monTotalMaterialCost
1 Kitchen Remodel 3 Complete 881.75
I need the total of all 5 outputs into one final total into monTotalMaterialCost
*/
The needed output:
intJobID = 1 strJobDescription = Kitchen Remodel strJobStatus = Complete monTotalMaterialCost = 881.75
What I'm Getting:
intJobID = 1 strJobDescription = Kitchen Remodel strJobStatus = Complete monTotalMaterialCost = 7391.80
What is happening: I have 5 different Materials from TJM.intMaterialQuantity. Each value from each row is 50, 20, 20, 5, 35 Then I have 5 different Cost for each material from TM.monMaterialCost those values matching in same order 4.99,12.99, 1.89, 32.00, 4.99. I am getting the 50+20+20+5+35 = 130 and 4.99+12.99+1.89+32.00+4.99 = 56.86 So I am getting in the final equation 130 * 56.86 = 7,391.80.
What I need is 50 * 4.99 = 249.50, 20 * 12.99 = 259.80, 20 * 1.89 = 37.80, 5 * 32.00 = 160, 35 * 4.99 = 174.65 The I need to add all of those totals together for a total amount spent on materials for the job for a total of 881.75.
I hope this helps better understand my question.
I have figured it out. I have posted correct answer for future questions:
SELECT
TJ.intJobID
,TJ.strJobDescription
,TJ.intJobStatusID
,TJS.strJobStatus
, SUM (TJM.intMaterialQuantity * TM.monMaterialCost) AS monTotalMaterialCost
FROM TJobs AS TJ
join TJobMaterials AS TJM on TJM.intJobID = TJ.intJobID
join TMaterials AS TM on TJM.intMaterialID = TM.intMaterialID
join TJobStatus AS TJS on TJ.intJobStatusID = TJS.intJobStatusID
GROUP BY
TJ.intJobID
,TJ.strJobDescription
,TJ.intJobStatusID
,TJS.strJobStatus
sql
sql-server sql
sql-server asked Dec 12 '16 at 17:49
Brad Wethington
426
asked Dec 12 '16 at 17:49
Brad Wethington
426
edited Dec 13 '16 at 18:00
asked Dec 12 '16 at 17:49
Brad Wethington
426
asked Dec 12 '16 at 17:49
Brad Wethington
426
asked Dec 12 '16 at 17:49
Brad Wethington
426
426
1
If you can give a sample input and a sample output it would be easier to help you
– gh9
Dec 12 '16 at 17:50
3
Just delete columns TJM.intMaterialQuantity, TM.monMaterialCost from GROUP BY clause
– Igor Borisenko
Dec 12 '16 at 18:03
@IgorBorisenko that doesn't work. I get 73910.80. The total should be 3,138.75
– Brad Wethington
Dec 13 '16 at 0:02
@TonyDong I'm not sure what that means.
– Brad Wethington
Dec 13 '16 at 0:02
@gh9 I have added the inputs for the materials and the materials used in job 1.
– Brad Wethington
Dec 13 '16 at 0:08
|
show 2 more comments
1
If you can give a sample input and a sample output it would be easier to help you
– gh9
Dec 12 '16 at 17:50
3
Just delete columns TJM.intMaterialQuantity, TM.monMaterialCost from GROUP BY clause
– Igor Borisenko
Dec 12 '16 at 18:03
@IgorBorisenko that doesn't work. I get 73910.80. The total should be 3,138.75
– Brad Wethington
Dec 13 '16 at 0:02
@TonyDong I'm not sure what that means.
– Brad Wethington
Dec 13 '16 at 0:02
@gh9 I have added the inputs for the materials and the materials used in job 1.
– Brad Wethington
Dec 13 '16 at 0:08
1
1
If you can give a sample input and a sample output it would be easier to help you
– gh9
Dec 12 '16 at 17:50
If you can give a sample input and a sample output it would be easier to help you
– gh9
Dec 12 '16 at 17:50
3
3
Just delete columns TJM.intMaterialQuantity, TM.monMaterialCost from GROUP BY clause
– Igor Borisenko
Dec 12 '16 at 18:03
Just delete columns TJM.intMaterialQuantity, TM.monMaterialCost from GROUP BY clause
– Igor Borisenko
Dec 12 '16 at 18:03
@IgorBorisenko that doesn't work. I get 73910.80. The total should be 3,138.75
– Brad Wethington
Dec 13 '16 at 0:02
@IgorBorisenko that doesn't work. I get 73910.80. The total should be 3,138.75
– Brad Wethington
Dec 13 '16 at 0:02
@TonyDong I'm not sure what that means.
– Brad Wethington
Dec 13 '16 at 0:02
@TonyDong I'm not sure what that means.
– Brad Wethington
Dec 13 '16 at 0:02
@gh9 I have added the inputs for the materials and the materials used in job 1.
– Brad Wethington
Dec 13 '16 at 0:08
@gh9 I have added the inputs for the materials and the materials used in job 1.
– Brad Wethington
Dec 13 '16 at 0:08
|
show 2 more comments
1 Answer
1
active
oldest
votes
up vote
1
down vote
You really should stop using the antiquated ANSI-89 style joins. The ANSI-92 style joins have been around for 25 years now. They are much simpler to read and prevents a lot of accidental mistakes.
- Bad habits to kick : using old-style JOINs
The second half of your query would look like this.
FROM TJobs AS TJ
join TJobCustomers AS TJC on TJ.intJobID = TJC.intJobID
join TCustomers AS TC on TJC.intCustomerID = TC.intCustomerID
join TJobMaterials AS TJM on TJM.intJobID = TJ.intJobID
join TMaterials AS TM on TJM.intMaterialID = TM.intMaterialID
WHERE TJ.intJobID = 1
edited Nov 10 at 16:07
Aaron Bertrand
205k27357401
answered Dec 12 '16 at 19:30
Sean Lange
24.2k21735
Thanks for the advise! I'm still learning. This gives me each materials total in each row. This gives me 5 rows. I need the total of all 5 rows into one column in one row. For example, This query gives me 37.80, 174.65, 249.50, 259.80, 160.00 These are the totals for each different material multiplied by their quantity. I then need to add all of those together so I show the one row giving the total of all materials for the job.
– Brad Wethington
Dec 13 '16 at 0:19
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
You really should stop using the antiquated ANSI-89 style joins. The ANSI-92 style joins have been around for 25 years now. They are much simpler to read and prevents a lot of accidental mistakes.
- Bad habits to kick : using old-style JOINs
The second half of your query would look like this.
FROM TJobs AS TJ
join TJobCustomers AS TJC on TJ.intJobID = TJC.intJobID
join TCustomers AS TC on TJC.intCustomerID = TC.intCustomerID
join TJobMaterials AS TJM on TJM.intJobID = TJ.intJobID
join TMaterials AS TM on TJM.intMaterialID = TM.intMaterialID
WHERE TJ.intJobID = 1
edited Nov 10 at 16:07
Aaron Bertrand
205k27357401
answered Dec 12 '16 at 19:30
Sean Lange
24.2k21735
Thanks for the advise! I'm still learning. This gives me each materials total in each row. This gives me 5 rows. I need the total of all 5 rows into one column in one row. For example, This query gives me 37.80, 174.65, 249.50, 259.80, 160.00 These are the totals for each different material multiplied by their quantity. I then need to add all of those together so I show the one row giving the total of all materials for the job.
– Brad Wethington
Dec 13 '16 at 0:19
add a comment |
up vote
1
down vote
You really should stop using the antiquated ANSI-89 style joins. The ANSI-92 style joins have been around for 25 years now. They are much simpler to read and prevents a lot of accidental mistakes.
- Bad habits to kick : using old-style JOINs
The second half of your query would look like this.
FROM TJobs AS TJ
join TJobCustomers AS TJC on TJ.intJobID = TJC.intJobID
join TCustomers AS TC on TJC.intCustomerID = TC.intCustomerID
join TJobMaterials AS TJM on TJM.intJobID = TJ.intJobID
join TMaterials AS TM on TJM.intMaterialID = TM.intMaterialID
WHERE TJ.intJobID = 1
edited Nov 10 at 16:07
Aaron Bertrand
205k27357401
answered Dec 12 '16 at 19:30
Sean Lange
24.2k21735
Thanks for the advise! I'm still learning. This gives me each materials total in each row. This gives me 5 rows. I need the total of all 5 rows into one column in one row. For example, This query gives me 37.80, 174.65, 249.50, 259.80, 160.00 These are the totals for each different material multiplied by their quantity. I then need to add all of those together so I show the one row giving the total of all materials for the job.
– Brad Wethington
Dec 13 '16 at 0:19
add a comment |
up vote
1
down vote
up vote
1
down vote
You really should stop using the antiquated ANSI-89 style joins. The ANSI-92 style joins have been around for 25 years now. They are much simpler to read and prevents a lot of accidental mistakes.
- Bad habits to kick : using old-style JOINs
The second half of your query would look like this.
FROM TJobs AS TJ
join TJobCustomers AS TJC on TJ.intJobID = TJC.intJobID
join TCustomers AS TC on TJC.intCustomerID = TC.intCustomerID
join TJobMaterials AS TJM on TJM.intJobID = TJ.intJobID
join TMaterials AS TM on TJM.intMaterialID = TM.intMaterialID
WHERE TJ.intJobID = 1
edited Nov 10 at 16:07
Aaron Bertrand
205k27357401
answered Dec 12 '16 at 19:30
Sean Lange
24.2k21735
You really should stop using the antiquated ANSI-89 style joins. The ANSI-92 style joins have been around for 25 years now. They are much simpler to read and prevents a lot of accidental mistakes.
- Bad habits to kick : using old-style JOINs
The second half of your query would look like this.
FROM TJobs AS TJ
join TJobCustomers AS TJC on TJ.intJobID = TJC.intJobID
join TCustomers AS TC on TJC.intCustomerID = TC.intCustomerID
join TJobMaterials AS TJM on TJM.intJobID = TJ.intJobID
join TMaterials AS TM on TJM.intMaterialID = TM.intMaterialID
WHERE TJ.intJobID = 1
edited Nov 10 at 16:07
Aaron Bertrand
205k27357401
answered Dec 12 '16 at 19:30
Sean Lange
24.2k21735
edited Nov 10 at 16:07
Aaron Bertrand
205k27357401
edited Nov 10 at 16:07
Aaron Bertrand
205k27357401
edited Nov 10 at 16:07
Aaron Bertrand
205k27357401
205k27357401
answered Dec 12 '16 at 19:30
Sean Lange
24.2k21735
answered Dec 12 '16 at 19:30
Sean Lange
24.2k21735
answered Dec 12 '16 at 19:30
Sean Lange
24.2k21735
24.2k21735
Thanks for the advise! I'm still learning. This gives me each materials total in each row. This gives me 5 rows. I need the total of all 5 rows into one column in one row. For example, This query gives me 37.80, 174.65, 249.50, 259.80, 160.00 These are the totals for each different material multiplied by their quantity. I then need to add all of those together so I show the one row giving the total of all materials for the job.
– Brad Wethington
Dec 13 '16 at 0:19
add a comment |
Thanks for the advise! I'm still learning. This gives me each materials total in each row. This gives me 5 rows. I need the total of all 5 rows into one column in one row. For example, This query gives me 37.80, 174.65, 249.50, 259.80, 160.00 These are the totals for each different material multiplied by their quantity. I then need to add all of those together so I show the one row giving the total of all materials for the job.
– Brad Wethington
Dec 13 '16 at 0:19
Thanks for the advise! I'm still learning. This gives me each materials total in each row. This gives me 5 rows. I need the total of all 5 rows into one column in one row. For example, This query gives me 37.80, 174.65, 249.50, 259.80, 160.00 These are the totals for each different material multiplied by their quantity. I then need to add all of those together so I show the one row giving the total of all materials for the job.
– Brad Wethington
Dec 13 '16 at 0:19
Thanks for the advise! I'm still learning. This gives me each materials total in each row. This gives me 5 rows. I need the total of all 5 rows into one column in one row. For example, This query gives me 37.80, 174.65, 249.50, 259.80, 160.00 These are the totals for each different material multiplied by their quantity. I then need to add all of those together so I show the one row giving the total of all materials for the job.
– Brad Wethington
Dec 13 '16 at 0:19
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f41106576%2fhow-to-add-total-of-column-together%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
If you can give a sample input and a sample output it would be easier to help you
– gh9
Dec 12 '16 at 17:50
3
Just delete columns TJM.intMaterialQuantity, TM.monMaterialCost from GROUP BY clause
– Igor Borisenko
Dec 12 '16 at 18:03
@IgorBorisenko that doesn't work. I get 73910.80. The total should be 3,138.75
– Brad Wethington
Dec 13 '16 at 0:02
@TonyDong I'm not sure what that means.
– Brad Wethington
Dec 13 '16 at 0:02
@gh9 I have added the inputs for the materials and the materials used in job 1.
– Brad Wethington
Dec 13 '16 at 0:08