Sort Records comparing sums across mulitple tables
0
Updated this Request so its easy to see what results are expected.
I am completely stuck, In SQL Server, I am wanting to bring back all jobs where
(
SUM(Order.InvoicesReceived) > (SUM(Estimate.GrossValue) + SUM (AdditionalEstimate.GrossValue))
) OR (
SUM(Order.ContractGiven) > (SUM(Estimate.GrossValue) + SUM (AdditionalEstimate.GrossValue))
)
CREATE TABLE Job (id INT, userid INT)
INSERT INTO Job ( id ,userid)VALUES ( 1,1)
INSERT INTO Job ( id ,userid)VALUES ( 2,1)
INSERT INTO Job ( id ,userid)VALUES ( 3,2)
INSERT INTO Job ( id ,userid)VALUES ( 4,2)
INSERT INTO Job ( id ,userid)VALUES ( 5,1)
CREATE TABLE [User] (id INT, UserName NVARCHAR (30))
INSERT INTO [User] ( id ,UserName)VALUES ( 1,'Richard')
INSERT INTO [User] ( id ,UserName)VALUES ( 2,'Jane')
CREATE Table Estimate (id INT, [job] INT, [GrossValue] DECIMAL (18,2))
INSERT INTO Estimate ( id ,[job], GrossValue)VALUES ( 1,3, 100)
INSERT INTO Estimate ( id ,[job], GrossValue)VALUES ( 2,4, 100)
INSERT INTO Estimate ( id ,[job], GrossValue)VALUES ( 3,5, 200)
INSERT INTO Estimate ( id ,[job], GrossValue)VALUES ( 4,5, 200)
CREATE Table AdditionalEstimate (id INT, [job] INT, [GrossValue] DECIMAL (18,2))
INSERT INTO AdditionalEstimate ( id ,[job], GrossValue)VALUES ( 1,1, 100)
INSERT INTO AdditionalEstimate ( id ,[job], GrossValue)VALUES ( 2,2, 100)
INSERT INTO AdditionalEstimate ( id ,[job], GrossValue)VALUES ( 3,5, 100)
INSERT INTO AdditionalEstimate ( id ,[job], GrossValue)VALUES ( 4,5, 100)
CREATE Table [Order] (id INT, [job] INT, ContractGiven DECIMAL (18,2), InvoicesReceived DECIMAL (18,2))
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES ( 1,1, 50, 0)
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES ( 2,2, 150, 0)
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES ( 3,3, 50, 0)
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES ( 4,4, 150, 0)
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES ( 5,5, 400, 0)
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES ( 6,5, 100, 0)
To make it easy to see what results I should get I added the following table and updated the inserts into tables.
JOB Estimate AddEstimate Order
1 Null 100 50
2 Null 100 150
3 100 NULL 50
4 100 NULL 150
5 200 100 500
5 200 100 NA
InvoicedRecieved is ignored for simplicity.
Jobs 2,4 Should be returned.
Richard 1, Jane 1
I also need another statement bring back the number of jobs per user that met the above criteria.
Any help would be greatly appreciated
sql
sql-server asked Nov 9 '18 at 2:18
RobertRobert
32
|
show 3 more comments
0
Updated this Request so its easy to see what results are expected.
I am completely stuck, In SQL Server, I am wanting to bring back all jobs where
(
SUM(Order.InvoicesReceived) > (SUM(Estimate.GrossValue) + SUM (AdditionalEstimate.GrossValue))
) OR (
SUM(Order.ContractGiven) > (SUM(Estimate.GrossValue) + SUM (AdditionalEstimate.GrossValue))
)
CREATE TABLE Job (id INT, userid INT)
INSERT INTO Job ( id ,userid)VALUES ( 1,1)
INSERT INTO Job ( id ,userid)VALUES ( 2,1)
INSERT INTO Job ( id ,userid)VALUES ( 3,2)
INSERT INTO Job ( id ,userid)VALUES ( 4,2)
INSERT INTO Job ( id ,userid)VALUES ( 5,1)
CREATE TABLE [User] (id INT, UserName NVARCHAR (30))
INSERT INTO [User] ( id ,UserName)VALUES ( 1,'Richard')
INSERT INTO [User] ( id ,UserName)VALUES ( 2,'Jane')
CREATE Table Estimate (id INT, [job] INT, [GrossValue] DECIMAL (18,2))
INSERT INTO Estimate ( id ,[job], GrossValue)VALUES ( 1,3, 100)
INSERT INTO Estimate ( id ,[job], GrossValue)VALUES ( 2,4, 100)
INSERT INTO Estimate ( id ,[job], GrossValue)VALUES ( 3,5, 200)
INSERT INTO Estimate ( id ,[job], GrossValue)VALUES ( 4,5, 200)
CREATE Table AdditionalEstimate (id INT, [job] INT, [GrossValue] DECIMAL (18,2))
INSERT INTO AdditionalEstimate ( id ,[job], GrossValue)VALUES ( 1,1, 100)
INSERT INTO AdditionalEstimate ( id ,[job], GrossValue)VALUES ( 2,2, 100)
INSERT INTO AdditionalEstimate ( id ,[job], GrossValue)VALUES ( 3,5, 100)
INSERT INTO AdditionalEstimate ( id ,[job], GrossValue)VALUES ( 4,5, 100)
CREATE Table [Order] (id INT, [job] INT, ContractGiven DECIMAL (18,2), InvoicesReceived DECIMAL (18,2))
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES ( 1,1, 50, 0)
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES ( 2,2, 150, 0)
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES ( 3,3, 50, 0)
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES ( 4,4, 150, 0)
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES ( 5,5, 400, 0)
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES ( 6,5, 100, 0)
To make it easy to see what results I should get I added the following table and updated the inserts into tables.
JOB Estimate AddEstimate Order
1 Null 100 50
2 Null 100 150
3 100 NULL 50
4 100 NULL 150
5 200 100 500
5 200 100 NA
InvoicedRecieved is ignored for simplicity.
Jobs 2,4 Should be returned.
Richard 1, Jane 1
I also need another statement bring back the number of jobs per user that met the above criteria.
Any help would be greatly appreciated
sql
sql-server asked Nov 9 '18 at 2:18
RobertRobert
32
What's your expect result?
– D-Shih
Nov 9 '18 at 2:20
1
That's<br/>annoying.. Could you remove it?
– dwir182
Nov 9 '18 at 2:23
Sorry about the <br/>. First time posting and it was the only way I could see to make the post readable. It is now updated.
– Robert
Nov 12 '18 at 22:56
Expected Results have been updated
– Robert
Nov 12 '18 at 22:56
Thank you for pointing that out. Have fixed it up. The code still brings back the same results
– Robert
Nov 13 '18 at 2:21
|
show 3 more comments
0
0
0
1
Updated this Request so its easy to see what results are expected.
I am completely stuck, In SQL Server, I am wanting to bring back all jobs where
(
SUM(Order.InvoicesReceived) > (SUM(Estimate.GrossValue) + SUM (AdditionalEstimate.GrossValue))
) OR (
SUM(Order.ContractGiven) > (SUM(Estimate.GrossValue) + SUM (AdditionalEstimate.GrossValue))
)
CREATE TABLE Job (id INT, userid INT)
INSERT INTO Job ( id ,userid)VALUES ( 1,1)
INSERT INTO Job ( id ,userid)VALUES ( 2,1)
INSERT INTO Job ( id ,userid)VALUES ( 3,2)
INSERT INTO Job ( id ,userid)VALUES ( 4,2)
INSERT INTO Job ( id ,userid)VALUES ( 5,1)
CREATE TABLE [User] (id INT, UserName NVARCHAR (30))
INSERT INTO [User] ( id ,UserName)VALUES ( 1,'Richard')
INSERT INTO [User] ( id ,UserName)VALUES ( 2,'Jane')
CREATE Table Estimate (id INT, [job] INT, [GrossValue] DECIMAL (18,2))
INSERT INTO Estimate ( id ,[job], GrossValue)VALUES ( 1,3, 100)
INSERT INTO Estimate ( id ,[job], GrossValue)VALUES ( 2,4, 100)
INSERT INTO Estimate ( id ,[job], GrossValue)VALUES ( 3,5, 200)
INSERT INTO Estimate ( id ,[job], GrossValue)VALUES ( 4,5, 200)
CREATE Table AdditionalEstimate (id INT, [job] INT, [GrossValue] DECIMAL (18,2))
INSERT INTO AdditionalEstimate ( id ,[job], GrossValue)VALUES ( 1,1, 100)
INSERT INTO AdditionalEstimate ( id ,[job], GrossValue)VALUES ( 2,2, 100)
INSERT INTO AdditionalEstimate ( id ,[job], GrossValue)VALUES ( 3,5, 100)
INSERT INTO AdditionalEstimate ( id ,[job], GrossValue)VALUES ( 4,5, 100)
CREATE Table [Order] (id INT, [job] INT, ContractGiven DECIMAL (18,2), InvoicesReceived DECIMAL (18,2))
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES ( 1,1, 50, 0)
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES ( 2,2, 150, 0)
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES ( 3,3, 50, 0)
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES ( 4,4, 150, 0)
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES ( 5,5, 400, 0)
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES ( 6,5, 100, 0)
To make it easy to see what results I should get I added the following table and updated the inserts into tables.
JOB Estimate AddEstimate Order
1 Null 100 50
2 Null 100 150
3 100 NULL 50
4 100 NULL 150
5 200 100 500
5 200 100 NA
InvoicedRecieved is ignored for simplicity.
Jobs 2,4 Should be returned.
Richard 1, Jane 1
I also need another statement bring back the number of jobs per user that met the above criteria.
Any help would be greatly appreciated
sql
sql-server asked Nov 9 '18 at 2:18
RobertRobert
32
Updated this Request so its easy to see what results are expected.
I am completely stuck, In SQL Server, I am wanting to bring back all jobs where
(
SUM(Order.InvoicesReceived) > (SUM(Estimate.GrossValue) + SUM (AdditionalEstimate.GrossValue))
) OR (
SUM(Order.ContractGiven) > (SUM(Estimate.GrossValue) + SUM (AdditionalEstimate.GrossValue))
)
CREATE TABLE Job (id INT, userid INT)
INSERT INTO Job ( id ,userid)VALUES ( 1,1)
INSERT INTO Job ( id ,userid)VALUES ( 2,1)
INSERT INTO Job ( id ,userid)VALUES ( 3,2)
INSERT INTO Job ( id ,userid)VALUES ( 4,2)
INSERT INTO Job ( id ,userid)VALUES ( 5,1)
CREATE TABLE [User] (id INT, UserName NVARCHAR (30))
INSERT INTO [User] ( id ,UserName)VALUES ( 1,'Richard')
INSERT INTO [User] ( id ,UserName)VALUES ( 2,'Jane')
CREATE Table Estimate (id INT, [job] INT, [GrossValue] DECIMAL (18,2))
INSERT INTO Estimate ( id ,[job], GrossValue)VALUES ( 1,3, 100)
INSERT INTO Estimate ( id ,[job], GrossValue)VALUES ( 2,4, 100)
INSERT INTO Estimate ( id ,[job], GrossValue)VALUES ( 3,5, 200)
INSERT INTO Estimate ( id ,[job], GrossValue)VALUES ( 4,5, 200)
CREATE Table AdditionalEstimate (id INT, [job] INT, [GrossValue] DECIMAL (18,2))
INSERT INTO AdditionalEstimate ( id ,[job], GrossValue)VALUES ( 1,1, 100)
INSERT INTO AdditionalEstimate ( id ,[job], GrossValue)VALUES ( 2,2, 100)
INSERT INTO AdditionalEstimate ( id ,[job], GrossValue)VALUES ( 3,5, 100)
INSERT INTO AdditionalEstimate ( id ,[job], GrossValue)VALUES ( 4,5, 100)
CREATE Table [Order] (id INT, [job] INT, ContractGiven DECIMAL (18,2), InvoicesReceived DECIMAL (18,2))
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES ( 1,1, 50, 0)
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES ( 2,2, 150, 0)
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES ( 3,3, 50, 0)
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES ( 4,4, 150, 0)
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES ( 5,5, 400, 0)
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES ( 6,5, 100, 0)
To make it easy to see what results I should get I added the following table and updated the inserts into tables.
JOB Estimate AddEstimate Order
1 Null 100 50
2 Null 100 150
3 100 NULL 50
4 100 NULL 150
5 200 100 500
5 200 100 NA
InvoicedRecieved is ignored for simplicity.
Jobs 2,4 Should be returned.
Richard 1, Jane 1
I also need another statement bring back the number of jobs per user that met the above criteria.
Any help would be greatly appreciated
sql
sql-server sql
sql-server asked Nov 9 '18 at 2:18
RobertRobert
32
asked Nov 9 '18 at 2:18
RobertRobert
32
edited Nov 13 '18 at 2:31
Robert
asked Nov 9 '18 at 2:18
RobertRobert
32
asked Nov 9 '18 at 2:18
RobertRobert
32
asked Nov 9 '18 at 2:18
RobertRobert
32
32
What's your expect result?
– D-Shih
Nov 9 '18 at 2:20
1
That's<br/>annoying.. Could you remove it?
– dwir182
Nov 9 '18 at 2:23
Sorry about the <br/>. First time posting and it was the only way I could see to make the post readable. It is now updated.
– Robert
Nov 12 '18 at 22:56
Expected Results have been updated
– Robert
Nov 12 '18 at 22:56
Thank you for pointing that out. Have fixed it up. The code still brings back the same results
– Robert
Nov 13 '18 at 2:21
|
show 3 more comments
What's your expect result?
– D-Shih
Nov 9 '18 at 2:20
1
That's<br/>annoying.. Could you remove it?
– dwir182
Nov 9 '18 at 2:23
Sorry about the <br/>. First time posting and it was the only way I could see to make the post readable. It is now updated.
– Robert
Nov 12 '18 at 22:56
Expected Results have been updated
– Robert
Nov 12 '18 at 22:56
Thank you for pointing that out. Have fixed it up. The code still brings back the same results
– Robert
Nov 13 '18 at 2:21
What's your expect result?
– D-Shih
Nov 9 '18 at 2:20
What's your expect result?
– D-Shih
Nov 9 '18 at 2:20
1
1
That's
<br/> annoying.. Could you remove it?– dwir182
Nov 9 '18 at 2:23
That's
<br/> annoying.. Could you remove it?– dwir182
Nov 9 '18 at 2:23
Sorry about the <br/>. First time posting and it was the only way I could see to make the post readable. It is now updated.
– Robert
Nov 12 '18 at 22:56
Sorry about the <br/>. First time posting and it was the only way I could see to make the post readable. It is now updated.
– Robert
Nov 12 '18 at 22:56
Expected Results have been updated
– Robert
Nov 12 '18 at 22:56
Expected Results have been updated
– Robert
Nov 12 '18 at 22:56
Thank you for pointing that out. Have fixed it up. The code still brings back the same results
– Robert
Nov 13 '18 at 2:21
Thank you for pointing that out. Have fixed it up. The code still brings back the same results
– Robert
Nov 13 '18 at 2:21
|
show 3 more comments
2 Answers
2
active
oldest
votes
0
Gordon was correct about the need to sum before comparing, due to the Cartesian product. However as his query isn't producing the correct result here is the query which does.
select J.id [Job]
, coalesce(O.InvoicesReceived,0) InvoicesReceived
, coalesce(O.ContractGiven,0) ContractGiven
, coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0) TotalQuoteCost
from Job J
left join (select Job, SUM(O.InvoicesReceived) InvoicesReceived, SUM(O.ContractGiven) ContractGiven from [Order] O group by Job) O on O.Job = J.id
left join (select Job, SUM(E.GrossValue) GrossValue from Estimate E group by Job) E on E.Job = J.id
left join (select Job, SUM(A.GrossValue) GrossValue from AdditionalEstimate A group by Job) A on A.Job = J.id
where (
coalesce(O.InvoicesReceived,0) > (coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0))
) OR (
coalesce(O.ContractGiven,0) > (coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0))
)
select [User Name], count(*)
from (
select U.UserName [User Name], J.id
, coalesce(O.InvoicesReceived,0) InvoicesReceived
, coalesce(O.ContractGiven,0) ContractGiven
, coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0) TotalQuoteCost
from Job J
inner join [User] U on U.id = J.UserId
left join (select Job, SUM(O.InvoicesReceived) InvoicesReceived, SUM(O.ContractGiven) ContractGiven from [Order] O group by Job) O on O.Job = J.id
left join (select Job, SUM(E.GrossValue) GrossValue from Estimate E group by Job) E on E.Job = J.id
left join (select Job, SUM(A.GrossValue) GrossValue from AdditionalEstimate A group by Job) A on A.Job = J.id
where (
coalesce(O.InvoicesReceived,0) > (coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0))
) OR (
coalesce(O.ContractGiven,0) > (coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0))
)
) x
group by [User Name]
DB Fiddle
answered Nov 9 '18 at 2:29
Dale BurrellDale Burrell
3,03732350
1
you might want to useISNULL()orCOALESCE()on the column beforeSUM(). Note that sum ofNULLvalue gives youNULL. It will affect the condition in theHAVINGclause. ExampleJob 2does not has an estimate
– Squirrel
Nov 9 '18 at 2:39
1
Thank you for this awesome solution. Hopefully people can give this a thumbs up as I was really in a bind
– Robert
Nov 21 '18 at 0:28
add a comment |
0
You need to aggregate before you join:
select U.UserName, count(*)
from [User] U join
Job j
on u.id = j.userid left join
(select o.job, sum(O.InvoicesReceived) as InvoicesReceived,
sum(o.ContractGiven) as ContractGiven
from [Order] O
group by o.job
) o
on o.Job = j.id left join
(select e.job, sum(e.GrossValue) as GrossValue
from estimate e
group by e.job
) e
on e.Job = j.id left join
(select ae.job, sum(ae.GrossValue) as GrossValue
from AdditionalEstimate ae
group by ae.job
) ae
on ae.Job = j.id
group by U.UserName
having coalesce(sum(o.InvoicesReceived), 0) > coalesce(sum(e.GrossValue), 0) + coalesce(sum(ae.GrossValue), 0) or
coalesce(sum(o.ContractGiven), 0) > coalesce(sum(e.GrossValue), 0) + coalesce(sum(ae.GrossValue), 0);
Otherwise, the joins will produce a Cartesian product and the sum()s will be off.
Here is a db<>fiddle.
If you want the jobs that meet these conditions:
select U.UserName, j.id
from [User] U join
Job j
on u.id = j.userid left join
(select o.job, sum(O.InvoicesReceived) as InvoicesReceived,
sum(o.ContractGiven) as ContractGiven
from [Order] O
group by o.job
) o
on o.Job = j.id left join
(select e.job, sum(e.GrossValue) as GrossValue
from estimate e
group by e.job
) e
on e.Job = j.id left join
(select ae.job, sum(ae.GrossValue) as GrossValue
from AdditionalEstimate ae
group by ae.job
) ae
on ae.Job = j.id
where coalesce(o.InvoicesReceived, 0) > coalesce(e.GrossValue, 0) + coalesce(ae.GrossValue, 0) or
coalesce(o.ContractGiven, 0) > coalesce(e.GrossValue, 0) + coalesce(ae.GrossValue, 0);
Which has this db<>fiddle.
answered Nov 9 '18 at 3:05
Gordon LinoffGordon Linoff
766k35297401
Thank you for the response. It gives me an idea how to proceed but the results are wrong. Updated my post so its clear what results are expected
– Robert
Nov 12 '18 at 23:03
If you run the select U.UserName, count(*) snippet it returns nothing which I presume is due to a null value eliminating the record. Does anyone know how to fix this?
– Robert
Nov 13 '18 at 2:48
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
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%2f53218920%2fsort-records-comparing-sums-across-mulitple-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Gordon was correct about the need to sum before comparing, due to the Cartesian product. However as his query isn't producing the correct result here is the query which does.
select J.id [Job]
, coalesce(O.InvoicesReceived,0) InvoicesReceived
, coalesce(O.ContractGiven,0) ContractGiven
, coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0) TotalQuoteCost
from Job J
left join (select Job, SUM(O.InvoicesReceived) InvoicesReceived, SUM(O.ContractGiven) ContractGiven from [Order] O group by Job) O on O.Job = J.id
left join (select Job, SUM(E.GrossValue) GrossValue from Estimate E group by Job) E on E.Job = J.id
left join (select Job, SUM(A.GrossValue) GrossValue from AdditionalEstimate A group by Job) A on A.Job = J.id
where (
coalesce(O.InvoicesReceived,0) > (coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0))
) OR (
coalesce(O.ContractGiven,0) > (coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0))
)
select [User Name], count(*)
from (
select U.UserName [User Name], J.id
, coalesce(O.InvoicesReceived,0) InvoicesReceived
, coalesce(O.ContractGiven,0) ContractGiven
, coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0) TotalQuoteCost
from Job J
inner join [User] U on U.id = J.UserId
left join (select Job, SUM(O.InvoicesReceived) InvoicesReceived, SUM(O.ContractGiven) ContractGiven from [Order] O group by Job) O on O.Job = J.id
left join (select Job, SUM(E.GrossValue) GrossValue from Estimate E group by Job) E on E.Job = J.id
left join (select Job, SUM(A.GrossValue) GrossValue from AdditionalEstimate A group by Job) A on A.Job = J.id
where (
coalesce(O.InvoicesReceived,0) > (coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0))
) OR (
coalesce(O.ContractGiven,0) > (coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0))
)
) x
group by [User Name]
DB Fiddle
answered Nov 9 '18 at 2:29
Dale BurrellDale Burrell
3,03732350
1
you might want to useISNULL()orCOALESCE()on the column beforeSUM(). Note that sum ofNULLvalue gives youNULL. It will affect the condition in theHAVINGclause. ExampleJob 2does not has an estimate
– Squirrel
Nov 9 '18 at 2:39
1
Thank you for this awesome solution. Hopefully people can give this a thumbs up as I was really in a bind
– Robert
Nov 21 '18 at 0:28
add a comment |
0
Gordon was correct about the need to sum before comparing, due to the Cartesian product. However as his query isn't producing the correct result here is the query which does.
select J.id [Job]
, coalesce(O.InvoicesReceived,0) InvoicesReceived
, coalesce(O.ContractGiven,0) ContractGiven
, coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0) TotalQuoteCost
from Job J
left join (select Job, SUM(O.InvoicesReceived) InvoicesReceived, SUM(O.ContractGiven) ContractGiven from [Order] O group by Job) O on O.Job = J.id
left join (select Job, SUM(E.GrossValue) GrossValue from Estimate E group by Job) E on E.Job = J.id
left join (select Job, SUM(A.GrossValue) GrossValue from AdditionalEstimate A group by Job) A on A.Job = J.id
where (
coalesce(O.InvoicesReceived,0) > (coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0))
) OR (
coalesce(O.ContractGiven,0) > (coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0))
)
select [User Name], count(*)
from (
select U.UserName [User Name], J.id
, coalesce(O.InvoicesReceived,0) InvoicesReceived
, coalesce(O.ContractGiven,0) ContractGiven
, coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0) TotalQuoteCost
from Job J
inner join [User] U on U.id = J.UserId
left join (select Job, SUM(O.InvoicesReceived) InvoicesReceived, SUM(O.ContractGiven) ContractGiven from [Order] O group by Job) O on O.Job = J.id
left join (select Job, SUM(E.GrossValue) GrossValue from Estimate E group by Job) E on E.Job = J.id
left join (select Job, SUM(A.GrossValue) GrossValue from AdditionalEstimate A group by Job) A on A.Job = J.id
where (
coalesce(O.InvoicesReceived,0) > (coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0))
) OR (
coalesce(O.ContractGiven,0) > (coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0))
)
) x
group by [User Name]
DB Fiddle
answered Nov 9 '18 at 2:29
Dale BurrellDale Burrell
3,03732350
1
you might want to useISNULL()orCOALESCE()on the column beforeSUM(). Note that sum ofNULLvalue gives youNULL. It will affect the condition in theHAVINGclause. ExampleJob 2does not has an estimate
– Squirrel
Nov 9 '18 at 2:39
1
Thank you for this awesome solution. Hopefully people can give this a thumbs up as I was really in a bind
– Robert
Nov 21 '18 at 0:28
add a comment |
0
0
0
Gordon was correct about the need to sum before comparing, due to the Cartesian product. However as his query isn't producing the correct result here is the query which does.
select J.id [Job]
, coalesce(O.InvoicesReceived,0) InvoicesReceived
, coalesce(O.ContractGiven,0) ContractGiven
, coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0) TotalQuoteCost
from Job J
left join (select Job, SUM(O.InvoicesReceived) InvoicesReceived, SUM(O.ContractGiven) ContractGiven from [Order] O group by Job) O on O.Job = J.id
left join (select Job, SUM(E.GrossValue) GrossValue from Estimate E group by Job) E on E.Job = J.id
left join (select Job, SUM(A.GrossValue) GrossValue from AdditionalEstimate A group by Job) A on A.Job = J.id
where (
coalesce(O.InvoicesReceived,0) > (coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0))
) OR (
coalesce(O.ContractGiven,0) > (coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0))
)
select [User Name], count(*)
from (
select U.UserName [User Name], J.id
, coalesce(O.InvoicesReceived,0) InvoicesReceived
, coalesce(O.ContractGiven,0) ContractGiven
, coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0) TotalQuoteCost
from Job J
inner join [User] U on U.id = J.UserId
left join (select Job, SUM(O.InvoicesReceived) InvoicesReceived, SUM(O.ContractGiven) ContractGiven from [Order] O group by Job) O on O.Job = J.id
left join (select Job, SUM(E.GrossValue) GrossValue from Estimate E group by Job) E on E.Job = J.id
left join (select Job, SUM(A.GrossValue) GrossValue from AdditionalEstimate A group by Job) A on A.Job = J.id
where (
coalesce(O.InvoicesReceived,0) > (coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0))
) OR (
coalesce(O.ContractGiven,0) > (coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0))
)
) x
group by [User Name]
DB Fiddle
answered Nov 9 '18 at 2:29
Dale BurrellDale Burrell
3,03732350
Gordon was correct about the need to sum before comparing, due to the Cartesian product. However as his query isn't producing the correct result here is the query which does.
select J.id [Job]
, coalesce(O.InvoicesReceived,0) InvoicesReceived
, coalesce(O.ContractGiven,0) ContractGiven
, coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0) TotalQuoteCost
from Job J
left join (select Job, SUM(O.InvoicesReceived) InvoicesReceived, SUM(O.ContractGiven) ContractGiven from [Order] O group by Job) O on O.Job = J.id
left join (select Job, SUM(E.GrossValue) GrossValue from Estimate E group by Job) E on E.Job = J.id
left join (select Job, SUM(A.GrossValue) GrossValue from AdditionalEstimate A group by Job) A on A.Job = J.id
where (
coalesce(O.InvoicesReceived,0) > (coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0))
) OR (
coalesce(O.ContractGiven,0) > (coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0))
)
select [User Name], count(*)
from (
select U.UserName [User Name], J.id
, coalesce(O.InvoicesReceived,0) InvoicesReceived
, coalesce(O.ContractGiven,0) ContractGiven
, coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0) TotalQuoteCost
from Job J
inner join [User] U on U.id = J.UserId
left join (select Job, SUM(O.InvoicesReceived) InvoicesReceived, SUM(O.ContractGiven) ContractGiven from [Order] O group by Job) O on O.Job = J.id
left join (select Job, SUM(E.GrossValue) GrossValue from Estimate E group by Job) E on E.Job = J.id
left join (select Job, SUM(A.GrossValue) GrossValue from AdditionalEstimate A group by Job) A on A.Job = J.id
where (
coalesce(O.InvoicesReceived,0) > (coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0))
) OR (
coalesce(O.ContractGiven,0) > (coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0))
)
) x
group by [User Name]
DB Fiddle
answered Nov 9 '18 at 2:29
Dale BurrellDale Burrell
3,03732350
edited Nov 19 '18 at 2:33
answered Nov 9 '18 at 2:29
Dale BurrellDale Burrell
3,03732350
answered Nov 9 '18 at 2:29
Dale BurrellDale Burrell
3,03732350
answered Nov 9 '18 at 2:29
Dale BurrellDale Burrell
3,03732350
3,03732350
1
you might want to useISNULL()orCOALESCE()on the column beforeSUM(). Note that sum ofNULLvalue gives youNULL. It will affect the condition in theHAVINGclause. ExampleJob 2does not has an estimate
– Squirrel
Nov 9 '18 at 2:39
1
Thank you for this awesome solution. Hopefully people can give this a thumbs up as I was really in a bind
– Robert
Nov 21 '18 at 0:28
add a comment |
1
you might want to useISNULL()orCOALESCE()on the column beforeSUM(). Note that sum ofNULLvalue gives youNULL. It will affect the condition in theHAVINGclause. ExampleJob 2does not has an estimate
– Squirrel
Nov 9 '18 at 2:39
1
Thank you for this awesome solution. Hopefully people can give this a thumbs up as I was really in a bind
– Robert
Nov 21 '18 at 0:28
1
1
you might want to use
ISNULL() or COALESCE() on the column before SUM(). Note that sum of NULL value gives you NULL. It will affect the condition in the HAVING clause. Example Job 2 does not has an estimate– Squirrel
Nov 9 '18 at 2:39
you might want to use
ISNULL() or COALESCE() on the column before SUM(). Note that sum of NULL value gives you NULL. It will affect the condition in the HAVING clause. Example Job 2 does not has an estimate– Squirrel
Nov 9 '18 at 2:39
1
1
Thank you for this awesome solution. Hopefully people can give this a thumbs up as I was really in a bind
– Robert
Nov 21 '18 at 0:28
Thank you for this awesome solution. Hopefully people can give this a thumbs up as I was really in a bind
– Robert
Nov 21 '18 at 0:28
add a comment |
0
You need to aggregate before you join:
select U.UserName, count(*)
from [User] U join
Job j
on u.id = j.userid left join
(select o.job, sum(O.InvoicesReceived) as InvoicesReceived,
sum(o.ContractGiven) as ContractGiven
from [Order] O
group by o.job
) o
on o.Job = j.id left join
(select e.job, sum(e.GrossValue) as GrossValue
from estimate e
group by e.job
) e
on e.Job = j.id left join
(select ae.job, sum(ae.GrossValue) as GrossValue
from AdditionalEstimate ae
group by ae.job
) ae
on ae.Job = j.id
group by U.UserName
having coalesce(sum(o.InvoicesReceived), 0) > coalesce(sum(e.GrossValue), 0) + coalesce(sum(ae.GrossValue), 0) or
coalesce(sum(o.ContractGiven), 0) > coalesce(sum(e.GrossValue), 0) + coalesce(sum(ae.GrossValue), 0);
Otherwise, the joins will produce a Cartesian product and the sum()s will be off.
Here is a db<>fiddle.
If you want the jobs that meet these conditions:
select U.UserName, j.id
from [User] U join
Job j
on u.id = j.userid left join
(select o.job, sum(O.InvoicesReceived) as InvoicesReceived,
sum(o.ContractGiven) as ContractGiven
from [Order] O
group by o.job
) o
on o.Job = j.id left join
(select e.job, sum(e.GrossValue) as GrossValue
from estimate e
group by e.job
) e
on e.Job = j.id left join
(select ae.job, sum(ae.GrossValue) as GrossValue
from AdditionalEstimate ae
group by ae.job
) ae
on ae.Job = j.id
where coalesce(o.InvoicesReceived, 0) > coalesce(e.GrossValue, 0) + coalesce(ae.GrossValue, 0) or
coalesce(o.ContractGiven, 0) > coalesce(e.GrossValue, 0) + coalesce(ae.GrossValue, 0);
Which has this db<>fiddle.
answered Nov 9 '18 at 3:05
Gordon LinoffGordon Linoff
766k35297401
Thank you for the response. It gives me an idea how to proceed but the results are wrong. Updated my post so its clear what results are expected
– Robert
Nov 12 '18 at 23:03
If you run the select U.UserName, count(*) snippet it returns nothing which I presume is due to a null value eliminating the record. Does anyone know how to fix this?
– Robert
Nov 13 '18 at 2:48
add a comment |
0
You need to aggregate before you join:
select U.UserName, count(*)
from [User] U join
Job j
on u.id = j.userid left join
(select o.job, sum(O.InvoicesReceived) as InvoicesReceived,
sum(o.ContractGiven) as ContractGiven
from [Order] O
group by o.job
) o
on o.Job = j.id left join
(select e.job, sum(e.GrossValue) as GrossValue
from estimate e
group by e.job
) e
on e.Job = j.id left join
(select ae.job, sum(ae.GrossValue) as GrossValue
from AdditionalEstimate ae
group by ae.job
) ae
on ae.Job = j.id
group by U.UserName
having coalesce(sum(o.InvoicesReceived), 0) > coalesce(sum(e.GrossValue), 0) + coalesce(sum(ae.GrossValue), 0) or
coalesce(sum(o.ContractGiven), 0) > coalesce(sum(e.GrossValue), 0) + coalesce(sum(ae.GrossValue), 0);
Otherwise, the joins will produce a Cartesian product and the sum()s will be off.
Here is a db<>fiddle.
If you want the jobs that meet these conditions:
select U.UserName, j.id
from [User] U join
Job j
on u.id = j.userid left join
(select o.job, sum(O.InvoicesReceived) as InvoicesReceived,
sum(o.ContractGiven) as ContractGiven
from [Order] O
group by o.job
) o
on o.Job = j.id left join
(select e.job, sum(e.GrossValue) as GrossValue
from estimate e
group by e.job
) e
on e.Job = j.id left join
(select ae.job, sum(ae.GrossValue) as GrossValue
from AdditionalEstimate ae
group by ae.job
) ae
on ae.Job = j.id
where coalesce(o.InvoicesReceived, 0) > coalesce(e.GrossValue, 0) + coalesce(ae.GrossValue, 0) or
coalesce(o.ContractGiven, 0) > coalesce(e.GrossValue, 0) + coalesce(ae.GrossValue, 0);
Which has this db<>fiddle.
answered Nov 9 '18 at 3:05
Gordon LinoffGordon Linoff
766k35297401
Thank you for the response. It gives me an idea how to proceed but the results are wrong. Updated my post so its clear what results are expected
– Robert
Nov 12 '18 at 23:03
If you run the select U.UserName, count(*) snippet it returns nothing which I presume is due to a null value eliminating the record. Does anyone know how to fix this?
– Robert
Nov 13 '18 at 2:48
add a comment |
0
0
0
You need to aggregate before you join:
select U.UserName, count(*)
from [User] U join
Job j
on u.id = j.userid left join
(select o.job, sum(O.InvoicesReceived) as InvoicesReceived,
sum(o.ContractGiven) as ContractGiven
from [Order] O
group by o.job
) o
on o.Job = j.id left join
(select e.job, sum(e.GrossValue) as GrossValue
from estimate e
group by e.job
) e
on e.Job = j.id left join
(select ae.job, sum(ae.GrossValue) as GrossValue
from AdditionalEstimate ae
group by ae.job
) ae
on ae.Job = j.id
group by U.UserName
having coalesce(sum(o.InvoicesReceived), 0) > coalesce(sum(e.GrossValue), 0) + coalesce(sum(ae.GrossValue), 0) or
coalesce(sum(o.ContractGiven), 0) > coalesce(sum(e.GrossValue), 0) + coalesce(sum(ae.GrossValue), 0);
Otherwise, the joins will produce a Cartesian product and the sum()s will be off.
Here is a db<>fiddle.
If you want the jobs that meet these conditions:
select U.UserName, j.id
from [User] U join
Job j
on u.id = j.userid left join
(select o.job, sum(O.InvoicesReceived) as InvoicesReceived,
sum(o.ContractGiven) as ContractGiven
from [Order] O
group by o.job
) o
on o.Job = j.id left join
(select e.job, sum(e.GrossValue) as GrossValue
from estimate e
group by e.job
) e
on e.Job = j.id left join
(select ae.job, sum(ae.GrossValue) as GrossValue
from AdditionalEstimate ae
group by ae.job
) ae
on ae.Job = j.id
where coalesce(o.InvoicesReceived, 0) > coalesce(e.GrossValue, 0) + coalesce(ae.GrossValue, 0) or
coalesce(o.ContractGiven, 0) > coalesce(e.GrossValue, 0) + coalesce(ae.GrossValue, 0);
Which has this db<>fiddle.
answered Nov 9 '18 at 3:05
Gordon LinoffGordon Linoff
766k35297401
You need to aggregate before you join:
select U.UserName, count(*)
from [User] U join
Job j
on u.id = j.userid left join
(select o.job, sum(O.InvoicesReceived) as InvoicesReceived,
sum(o.ContractGiven) as ContractGiven
from [Order] O
group by o.job
) o
on o.Job = j.id left join
(select e.job, sum(e.GrossValue) as GrossValue
from estimate e
group by e.job
) e
on e.Job = j.id left join
(select ae.job, sum(ae.GrossValue) as GrossValue
from AdditionalEstimate ae
group by ae.job
) ae
on ae.Job = j.id
group by U.UserName
having coalesce(sum(o.InvoicesReceived), 0) > coalesce(sum(e.GrossValue), 0) + coalesce(sum(ae.GrossValue), 0) or
coalesce(sum(o.ContractGiven), 0) > coalesce(sum(e.GrossValue), 0) + coalesce(sum(ae.GrossValue), 0);
Otherwise, the joins will produce a Cartesian product and the sum()s will be off.
Here is a db<>fiddle.
If you want the jobs that meet these conditions:
select U.UserName, j.id
from [User] U join
Job j
on u.id = j.userid left join
(select o.job, sum(O.InvoicesReceived) as InvoicesReceived,
sum(o.ContractGiven) as ContractGiven
from [Order] O
group by o.job
) o
on o.Job = j.id left join
(select e.job, sum(e.GrossValue) as GrossValue
from estimate e
group by e.job
) e
on e.Job = j.id left join
(select ae.job, sum(ae.GrossValue) as GrossValue
from AdditionalEstimate ae
group by ae.job
) ae
on ae.Job = j.id
where coalesce(o.InvoicesReceived, 0) > coalesce(e.GrossValue, 0) + coalesce(ae.GrossValue, 0) or
coalesce(o.ContractGiven, 0) > coalesce(e.GrossValue, 0) + coalesce(ae.GrossValue, 0);
Which has this db<>fiddle.
answered Nov 9 '18 at 3:05
Gordon LinoffGordon Linoff
766k35297401
edited Nov 9 '18 at 3:33
answered Nov 9 '18 at 3:05
Gordon LinoffGordon Linoff
766k35297401
answered Nov 9 '18 at 3:05
Gordon LinoffGordon Linoff
766k35297401
answered Nov 9 '18 at 3:05
Gordon LinoffGordon Linoff
766k35297401
766k35297401
Thank you for the response. It gives me an idea how to proceed but the results are wrong. Updated my post so its clear what results are expected
– Robert
Nov 12 '18 at 23:03
If you run the select U.UserName, count(*) snippet it returns nothing which I presume is due to a null value eliminating the record. Does anyone know how to fix this?
– Robert
Nov 13 '18 at 2:48
add a comment |
Thank you for the response. It gives me an idea how to proceed but the results are wrong. Updated my post so its clear what results are expected
– Robert
Nov 12 '18 at 23:03
If you run the select U.UserName, count(*) snippet it returns nothing which I presume is due to a null value eliminating the record. Does anyone know how to fix this?
– Robert
Nov 13 '18 at 2:48
Thank you for the response. It gives me an idea how to proceed but the results are wrong. Updated my post so its clear what results are expected
– Robert
Nov 12 '18 at 23:03
Thank you for the response. It gives me an idea how to proceed but the results are wrong. Updated my post so its clear what results are expected
– Robert
Nov 12 '18 at 23:03
If you run the select U.UserName, count(*) snippet it returns nothing which I presume is due to a null value eliminating the record. Does anyone know how to fix this?
– Robert
Nov 13 '18 at 2:48
If you run the select U.UserName, count(*) snippet it returns nothing which I presume is due to a null value eliminating the record. Does anyone know how to fix this?
– Robert
Nov 13 '18 at 2:48
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
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%2f53218920%2fsort-records-comparing-sums-across-mulitple-tables%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
What's your expect result?
– D-Shih
Nov 9 '18 at 2:20
1
That's
<br/>annoying.. Could you remove it?– dwir182
Nov 9 '18 at 2:23
Sorry about the <br/>. First time posting and it was the only way I could see to make the post readable. It is now updated.
– Robert
Nov 12 '18 at 22:56
Expected Results have been updated
– Robert
Nov 12 '18 at 22:56
Thank you for pointing that out. Have fixed it up. The code still brings back the same results
– Robert
Nov 13 '18 at 2:21