SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query to give sum of items with condition from multiple tables


Query to give sum of items with condition from multiple tables

Author
Message
rta t
rta t
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 165
I have these tables: LineItemSubParts, CostCodeSubCategories, CostCodes and LineItems
In the LineItemSubParts table there are fields named Quantity, MQuantity and Cost
In the CostCodes table there are field named CostCodeID and CostCode
In the LineItems table there is a field named JobId
I want to calculate the sum of the costs for a specific JobId but I want all the costcodeIDs to show up.
If the cost is not present for particular jobid then count should be 0 corresponding to that jobid.
Here is the query I wrote
SELECT CostCodeID, CostCode, CONVERT(money, SUM(Quantity * MQuantity * Cost)) As TotalCost
FROM tblLineItemSubParts
INNER JOIN tblCostCodeSubCategories ON tblLineItemSubParts.CostCodeSubCategoryID=tblCostCodeSubCategories.CostCodeSubCategoryID
INNER JOIN tblCostCodes ON tblCostCodeSubCategories.CostCodeID=tblCostCodes.CostCodeID
WHERE LineItemID IN (SELECT LineItemID FROM tblLineItems WHERE FKJobID=47)
GROUP BY PKCostCodeID, CostCode
Here is the result I got:

CostCodeID CostCode TotalCost
1 18-10-101 854.1248
3 18-10-103 386.67
4 18-10-104 1577.775
7 18-10-201 9026.675
15 58-10-100 100
25 18-10-405 0

What I want is to show all the CostCodeIDs show on the table even If the cost is not present for particular jobid then count should be 0 corresponding to that jobid.
Attached is the sample tbl
Attachments
tblCostCodes.xlsx (6 views, 8.00 KB)
bmg002
bmg002
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11577 Visits: 2222
You are missing some data there... or I misunderstand the request.
is the attached table the expected output, the sample input or something else? If it is the sample input, where are you getting the total cost from? All I see is a cost code and a cost code ID. If it is expected output, it is missing the total cost. I expect that it is one of the 3 tables in your original query (likely tblCostCodes). It is that particular join that is likely causing you greif though. You are doing an inner join so the only rows that will be returned are the ones where there is a match. Try changing your last inner join to a full outer join.

But without seeing some sample data, I am really just taking stabs in the dark.
rta t
rta t
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 165
Sorry I thought I attached all the tables.
Here are the other tables attached.
What I want to get is something like this:
CostCodeID CostCode TotalCost
1 18-10-101 854.1248
2 18-10-102 0
3 18-10-103 386.67
4 18-10-104 1577.775
5 18-10-105 0
6 18-10-106 0
7 18-10-201 9026.675
8 18-10-202 0
9 18-10-203 0
10 18-10-204 0
11 18-10-205 0
12 18-10-240 0
13 18-10-400 0
14 18-10-800 0
15 58-10-100 100
16 58-10-200 0

-
-
-upto 54 67-10-665 0
Attachments
tblLineItemSubParts.xlsx (1 view, 8.00 KB)
tblCostCodeSubCategory.xlsx (1 view, 9.00 KB)
tblLineItems.xlsx (2 views, 25.00 KB)
J Livingston SQL
J Livingston SQL
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23571 Visits: 39964
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

rta t
rta t
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 165
Thank you for the tip.
I will post it using the guide
bmg002
bmg002
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11577 Visits: 2222
for others wanting to help, here is the DDL
DECLARE @tblLineItems TABLE
(
[LineItemID] INT ,
[JobID] INT
);
DECLARE @tblCostCodes TABLE
(
[costCodeID] INT ,
[costCode] VARCHAR(25)
);
DECLARE @tblCostCodeSubCategories TABLE
(
[CostCodeSubCategoryID] INT ,
[CostCodeID] INT
);
DECLARE @tblLineItemSubParts TABLE
(
[LineItemSubPartID] INT ,
[LineItemID] INT ,
[Quantity] INT ,
[MQuantity] NUMERIC ,
[Cost] NUMERIC ,
[CostCodeSubCategoryID] INT
);

INSERT INTO @tblLineItems (
[LineItemID] ,
[JobID]
)
VALUES (
681, 46
) ,
(
682, 46
) ,
(
692, 47
) ,
(
694, 47
) ,
(
695, 47
) ,
(
696, 47
) ,
(
706, 48
) ,
(
707, 48
) ,
(
708, 48
) ,
(
709, 48
) ,
(
711, 50
) ,
(
712, 49
) ,
(
713, 49
) ,
(
743, 54
) ,
(
744, 54
) ,
(
745, 54
) ,
(
746, 54
) ,
(
747, 54
) ,
(
748, 54
) ,
(
749, 54
) ,
(
750, 54
) ,
(
751, 54
) ,
(
752, 54
) ,
(
753, 54
) ,
(
754, 54
) ,
(
755, 55
) ,
(
756, 55
) ,
(
768, 56
) ,
(
771, 57
) ,
(
772, 58
) ,
(
773, 58
);

INSERT INTO @tblLineItemSubParts (
[LineItemSubPartID] ,
[LineItemID] ,
[Quantity] ,
[MQuantity] ,
[Cost] ,
[CostCodeSubCategoryID]
)
VALUES (
293784, 400, 1, 200, 0, NULL
) ,
(
294194, 400, 1, 1, 500, NULL
) ,
(
294195, 400, 1, 1, 200, NULL
) ,
(
323271, 694, 1, 0, 0, NULL
) ,
(
323272, 694, 1, 0, 0, NULL
) ,
(
323273, 694, 1, 5.39, 3.095, 3
) ,
(
323274, 694, 1, 0, 0, NULL
) ,
(
323275, 694, 1, 8.98, 3.095, 3
) ,
(
323276, 694, 1, 0, 0, NULL
) ,
(
323277, 694, 1, 2.92, 3.095, 3
) ,
(
323278, 694, 1, 0, 0, NULL
) ,
(
323279, 694, 1, 2.92, 3.095, 3
) ,
(
323280, 694, 1, 0, 0, NULL
) ,
(
323281, 694, 1, 5.18, 3.095, 3
) ,
(
323282, 694, 1, 0, 0, NULL
) ,
(
323283, 694, 1, 2.63, 3.15, 3
) ,
(
323284, 694, 6, 0, 0, NULL
) ,
(
323285, 694, 6, 0, 0, NULL
) ,
(
323286, 694, 18, 1.11, 3.095, 3
) ,
(
323287, 694, 6, 0, 0, NULL
) ,
(
323288, 694, 6, 0.29, 3.095, 3
) ,
(
323289, 694, 6, 0, 0, NULL
) ,
(
323290, 694, 18, 1.11, 3.095, 3
) ,
(
323291, 694, 6, 0, 0, NULL
) ,
(
323292, 694, 18, 4.85, 3.095, 3
) ,
(
323293, 694, 6, 0, 0, NULL
) ,
(
323294, 694, 18, 4.85, 3.095, 3
);

INSERT INTO @tblCostCodeSubCategories (
[CostCodeSubCategoryID] ,
[CostCodeID]
)
VALUES (
1, 1
) ,
(
2, 1
) ,
(
3, 1
) ,
(
4, 2
) ,
(
5, 2
) ,
(
6, 3
) ,
(
7, 3
) ,
(
8, 3
) ,
(
9, 3
) ,
(
10, 3
) ,
(
11, 3
) ,
(
12, 3
) ,
(
13, 4
) ,
(
14, 5
) ,
(
15, 5
) ,
(
16, 6
) ,
(
17, 6
) ,
(
18, 6
) ,
(
19, 6
) ,
(
20, 6
) ,
(
21, 6
) ,
(
22, 6
) ,
(
23, 6
) ,
(
24, 6
) ,
(
25, 7
) ,
(
26, 8
) ,
(
27, 9
) ,
(
28, 10
) ,
(
29, 11
) ,
(
30, 12
) ,
(
31, 13
) ,
(
32, 14
) ,
(
33, 15
) ,
(
34, 16
) ,
(
35, 17
) ,
(
36, 18
) ,
(
37, 19
) ,
(
38, 20
) ,
(
39, 21
) ,
(
40, 22
) ,
(
41, 23
) ,
(
42, 24
) ,
(
43, 25
) ,
(
44, 29
) ,
(
45, 30
) ,
(
46, 30
) ,
(
47, 31
) ,
(
48, 31
) ,
(
49, 31
) ,
(
50, 31
) ,
(
51, 31
) ,
(
52, 31
) ,
(
53, 31
) ,
(
54, 31
) ,
(
55, 31
) ,
(
56, 32
) ,
(
57, 33
) ,
(
58, 34
) ,
(
59, 35
) ,
(
60, 36
) ,
(
61, 37
) ,
(
62, 38
) ,
(
63, 39
) ,
(
64, 40
) ,
(
65, 41
) ,
(
66, 42
) ,
(
67, 43
) ,
(
68, 44
) ,
(
69, 45
) ,
(
70, 46
) ,
(
71, 48
) ,
(
72, 50
) ,
(
73, 51
) ,
(
74, 52
) ,
(
75, 26
) ,
(
76, 26
) ,
(
77, 26
) ,
(
78, 27
) ,
(
79, 27
) ,
(
80, 28
) ,
(
81, 28
) ,
(
82, 28
) ,
(
83, 28
) ,
(
84, 28
) ,
(
85, 28
) ,
(
86, 28
) ,
(
87, 47
) ,
(
88, 49
) ,
(
89, 53
) ,
(
90, 54
) ,
(
91, 25
) ,
(
92, 52
) ,
(
93, 25
) ,
(
94, 52
) ,
(
95, 3
) ,
(
96, 28
);

INSERT INTO @tblCostCodes
VALUES (
1, '18-10-101'
) ,
(
2, '18-10-102'
) ,
(
3, '18-10-103'
) ,
(
4, '18-10-104'
) ,
(
5, '18-10-105'
) ,
(
6, '18-10-106'
) ,
(
7, '18-10-201'
) ,
(
8, '18-10-202'
) ,
(
9, '18-10-203'
) ,
(
10, '18-10-204'
) ,
(
11, '18-10-205'
) ,
(
12, '18-10-240'
) ,
(
13, '18-10-400'
) ,
(
14, '18-10-800'
) ,
(
15, '58-10-100'
) ,
(
16, '58-10-200'
) ,
(
17, '58-20-350'
) ,
(
18, '68-10-100'
) ,
(
19, '68-10-200'
) ,
(
20, '68-10-300'
) ,
(
21, '68-20-200'
) ,
(
22, '78-10-130'
) ,
(
23, '58-10-212'
) ,
(
24, '18-10-401'
) ,
(
25, '18-10-405'
) ,
(
26, '14-10-101'
) ,
(
27, '14-10-102'
) ,
(
28, '14-10-103'
) ,
(
29, '14-10-104'
) ,
(
30, '14-10-105'
) ,
(
31, '14-10-106'
) ,
(
32, '14-10-201'
) ,
(
33, '14-10-202'
) ,
(
34, '14-10-203'
) ,
(
35, '14-10-204'
) ,
(
36, '14-10-205'
) ,
(
37, '14-10-240'
) ,
(
38, '14-10-400'
) ,
(
39, '14-10-800'
) ,
(
40, '54-10-100'
) ,
(
41, '54-10-200'
) ,
(
42, '54-20-350'
) ,
(
43, '64-10-100'
) ,
(
44, '64-10-200'
) ,
(
45, '64-10-300'
) ,
(
46, '64-20-200'
) ,
(
47, '64-20-300'
) ,
(
48, '74-10-130'
) ,
(
49, '74-99-700'
) ,
(
50, '54-10-212'
) ,
(
51, '14-10-401'
) ,
(
52, '14-10-405'
) ,
(
53, '??-??-???'
) ,
(
54, '??-??-???'
);


I'm still working on the query to get what you want, but the query you provided does not produce the results you stated with the input provided best I can tell. When I run the query I get 1 result.

EDIT:
is this the results you were looking for:
SELECT
[CC].[costCodeID] ,
[CC].[costCode] ,
CONVERT(
MONEY ,
SUM([lisp].[Quantity] * [lisp].[MQuantity] * [lisp].[Cost])
) AS [TotalCost]
FROM
@tblLineItemSubParts [lisp]
FULL OUTER JOIN @tblCostCodeSubCategories [CCSC]
ON [CCSC].[CostCodeSubCategoryID] = [lisp].[CostCodeSubCategoryID]
FULL OUTER JOIN @tblCostCodes [CC]
ON [CCSC].[CostCodeID] = [CC].[costCodeID]
WHERE
[CC].[costCodeID] IS NOT NULL
GROUP BY
[CC].[costCodeID] ,
[CC].[costCode]
ORDER BY
[CC].[costCodeID];


I believe that is returning what you were looking for, no?

John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68617 Visits: 17534
rosarozina - Tuesday, November 14, 2017 9:26 AM
What I want is to show all the CostCodeIDs show on the table even If the cost is not present for particular jobid then count should be 0 corresponding to that jobid.

Is this not as simple as changing the first INNER JOIN to a LEFT JOIN?

A word of advice - alias your tables, and use the aliases to qualify every column name, whether in the SELECT list, the WHERE clause or a join predicate. It'll make your query easier to ready and guard against ambiguous column names in the future.

John

rta t
rta t
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 165
bmg002 - Wednesday, November 15, 2017 8:17 AM
for others wanting to help, here is the DDL
DECLARE @tblLineItems TABLE
(
[LineItemID] INT ,
[JobID] INT
);
DECLARE @tblCostCodes TABLE
(
[costCodeID] INT ,
[costCode] VARCHAR(25)
);
DECLARE @tblCostCodeSubCategories TABLE
(
[CostCodeSubCategoryID] INT ,
[CostCodeID] INT
);
DECLARE @tblLineItemSubParts TABLE
(
[LineItemSubPartID] INT ,
[LineItemID] INT ,
[Quantity] INT ,
[MQuantity] NUMERIC ,
[Cost] NUMERIC ,
[CostCodeSubCategoryID] INT
);

INSERT INTO @tblLineItems (
[LineItemID] ,
[JobID]
)
VALUES (
681, 46
) ,
(
682, 46
) ,
(
692, 47
) ,
(
694, 47
) ,
(
695, 47
) ,
(
696, 47
) ,
(
706, 48
) ,
(
707, 48
) ,
(
708, 48
) ,
(
709, 48
) ,
(
711, 50
) ,
(
712, 49
) ,
(
713, 49
) ,
(
743, 54
) ,
(
744, 54
) ,
(
745, 54
) ,
(
746, 54
) ,
(
747, 54
) ,
(
748, 54
) ,
(
749, 54
) ,
(
750, 54
) ,
(
751, 54
) ,
(
752, 54
) ,
(
753, 54
) ,
(
754, 54
) ,
(
755, 55
) ,
(
756, 55
) ,
(
768, 56
) ,
(
771, 57
) ,
(
772, 58
) ,
(
773, 58
);

INSERT INTO @tblLineItemSubParts (
[LineItemSubPartID] ,
[LineItemID] ,
[Quantity] ,
[MQuantity] ,
[Cost] ,
[CostCodeSubCategoryID]
)
VALUES (
293784, 400, 1, 200, 0, NULL
) ,
(
294194, 400, 1, 1, 500, NULL
) ,
(
294195, 400, 1, 1, 200, NULL
) ,
(
323271, 694, 1, 0, 0, NULL
) ,
(
323272, 694, 1, 0, 0, NULL
) ,
(
323273, 694, 1, 5.39, 3.095, 3
) ,
(
323274, 694, 1, 0, 0, NULL
) ,
(
323275, 694, 1, 8.98, 3.095, 3
) ,
(
323276, 694, 1, 0, 0, NULL
) ,
(
323277, 694, 1, 2.92, 3.095, 3
) ,
(
323278, 694, 1, 0, 0, NULL
) ,
(
323279, 694, 1, 2.92, 3.095, 3
) ,
(
323280, 694, 1, 0, 0, NULL
) ,
(
323281, 694, 1, 5.18, 3.095, 3
) ,
(
323282, 694, 1, 0, 0, NULL
) ,
(
323283, 694, 1, 2.63, 3.15, 3
) ,
(
323284, 694, 6, 0, 0, NULL
) ,
(
323285, 694, 6, 0, 0, NULL
) ,
(
323286, 694, 18, 1.11, 3.095, 3
) ,
(
323287, 694, 6, 0, 0, NULL
) ,
(
323288, 694, 6, 0.29, 3.095, 3
) ,
(
323289, 694, 6, 0, 0, NULL
) ,
(
323290, 694, 18, 1.11, 3.095, 3
) ,
(
323291, 694, 6, 0, 0, NULL
) ,
(
323292, 694, 18, 4.85, 3.095, 3
) ,
(
323293, 694, 6, 0, 0, NULL
) ,
(
323294, 694, 18, 4.85, 3.095, 3
);

INSERT INTO @tblCostCodeSubCategories (
[CostCodeSubCategoryID] ,
[CostCodeID]
)
VALUES (
1, 1
) ,
(
2, 1
) ,
(
3, 1
) ,
(
4, 2
) ,
(
5, 2
) ,
(
6, 3
) ,
(
7, 3
) ,
(
8, 3
) ,
(
9, 3
) ,
(
10, 3
) ,
(
11, 3
) ,
(
12, 3
) ,
(
13, 4
) ,
(
14, 5
) ,
(
15, 5
) ,
(
16, 6
) ,
(
17, 6
) ,
(
18, 6
) ,
(
19, 6
) ,
(
20, 6
) ,
(
21, 6
) ,
(
22, 6
) ,
(
23, 6
) ,
(
24, 6
) ,
(
25, 7
) ,
(
26, 8
) ,
(
27, 9
) ,
(
28, 10
) ,
(
29, 11
) ,
(
30, 12
) ,
(
31, 13
) ,
(
32, 14
) ,
(
33, 15
) ,
(
34, 16
) ,
(
35, 17
) ,
(
36, 18
) ,
(
37, 19
) ,
(
38, 20
) ,
(
39, 21
) ,
(
40, 22
) ,
(
41, 23
) ,
(
42, 24
) ,
(
43, 25
) ,
(
44, 29
) ,
(
45, 30
) ,
(
46, 30
) ,
(
47, 31
) ,
(
48, 31
) ,
(
49, 31
) ,
(
50, 31
) ,
(
51, 31
) ,
(
52, 31
) ,
(
53, 31
) ,
(
54, 31
) ,
(
55, 31
) ,
(
56, 32
) ,
(
57, 33
) ,
(
58, 34
) ,
(
59, 35
) ,
(
60, 36
) ,
(
61, 37
) ,
(
62, 38
) ,
(
63, 39
) ,
(
64, 40
) ,
(
65, 41
) ,
(
66, 42
) ,
(
67, 43
) ,
(
68, 44
) ,
(
69, 45
) ,
(
70, 46
) ,
(
71, 48
) ,
(
72, 50
) ,
(
73, 51
) ,
(
74, 52
) ,
(
75, 26
) ,
(
76, 26
) ,
(
77, 26
) ,
(
78, 27
) ,
(
79, 27
) ,
(
80, 28
) ,
(
81, 28
) ,
(
82, 28
) ,
(
83, 28
) ,
(
84, 28
) ,
(
85, 28
) ,
(
86, 28
) ,
(
87, 47
) ,
(
88, 49
) ,
(
89, 53
) ,
(
90, 54
) ,
(
91, 25
) ,
(
92, 52
) ,
(
93, 25
) ,
(
94, 52
) ,
(
95, 3
) ,
(
96, 28
);

INSERT INTO @tblCostCodes
VALUES (
1, '18-10-101'
) ,
(
2, '18-10-102'
) ,
(
3, '18-10-103'
) ,
(
4, '18-10-104'
) ,
(
5, '18-10-105'
) ,
(
6, '18-10-106'
) ,
(
7, '18-10-201'
) ,
(
8, '18-10-202'
) ,
(
9, '18-10-203'
) ,
(
10, '18-10-204'
) ,
(
11, '18-10-205'
) ,
(
12, '18-10-240'
) ,
(
13, '18-10-400'
) ,
(
14, '18-10-800'
) ,
(
15, '58-10-100'
) ,
(
16, '58-10-200'
) ,
(
17, '58-20-350'
) ,
(
18, '68-10-100'
) ,
(
19, '68-10-200'
) ,
(
20, '68-10-300'
) ,
(
21, '68-20-200'
) ,
(
22, '78-10-130'
) ,
(
23, '58-10-212'
) ,
(
24, '18-10-401'
) ,
(
25, '18-10-405'
) ,
(
26, '14-10-101'
) ,
(
27, '14-10-102'
) ,
(
28, '14-10-103'
) ,
(
29, '14-10-104'
) ,
(
30, '14-10-105'
) ,
(
31, '14-10-106'
) ,
(
32, '14-10-201'
) ,
(
33, '14-10-202'
) ,
(
34, '14-10-203'
) ,
(
35, '14-10-204'
) ,
(
36, '14-10-205'
) ,
(
37, '14-10-240'
) ,
(
38, '14-10-400'
) ,
(
39, '14-10-800'
) ,
(
40, '54-10-100'
) ,
(
41, '54-10-200'
) ,
(
42, '54-20-350'
) ,
(
43, '64-10-100'
) ,
(
44, '64-10-200'
) ,
(
45, '64-10-300'
) ,
(
46, '64-20-200'
) ,
(
47, '64-20-300'
) ,
(
48, '74-10-130'
) ,
(
49, '74-99-700'
) ,
(
50, '54-10-212'
) ,
(
51, '14-10-401'
) ,
(
52, '14-10-405'
) ,
(
53, '??-??-???'
) ,
(
54, '??-??-???'
);


I'm still working on the query to get what you want, but the query you provided does not produce the results you stated with the input provided best I can tell. When I run the query I get 1 result.

EDIT:
is this the results you were looking for:
SELECT
[CC].[costCodeID] ,
[CC].[costCode] ,
CONVERT(
MONEY ,
SUM([lisp].[Quantity] * [lisp].[MQuantity] * [lisp].[Cost])
) AS [TotalCost]
FROM
@tblLineItemSubParts [lisp]
FULL OUTER JOIN @tblCostCodeSubCategories [CCSC]
ON [CCSC].[CostCodeSubCategoryID] = [lisp].[CostCodeSubCategoryID]
FULL OUTER JOIN @tblCostCodes [CC]
ON [CCSC].[CostCodeID] = [CC].[costCodeID]
WHERE
[CC].[costCodeID] IS NOT NULL
GROUP BY
[CC].[costCodeID] ,
[CC].[costCode]
ORDER BY
[CC].[costCodeID];


I believe that is returning what you were looking for, no?

Thank you for the reply, it gave me the total of the Sum, What I wanted was the cost for each costcode id for the specific jobid. I will try to change the filtering and hopefully it will work. Thanks again

J Livingston SQL
J Livingston SQL
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23571 Visits: 39964
rosarozina - Tuesday, November 14, 2017 4:24 PM
Thank you for the tip.
I will post it using the guide

look forward to it....


________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search