ozbailer
asked on
SQL Group by with Summing in Sub-select
Hi All,
Quite new to the SQL Language and have been scratching my head with a group by with sub-select's summing some costs from another couple of tables.
Here is the statement;
SELECT Account_Id AS Expr1,
(SELECT SUM(TOTAL_COST) AS Expr2 FROM Job_Labour WHERE Job_Labour.JOB_NUMBER = Job_Unfinished.Job_Number)
(SELECT SUM(Amount) AS Expr3 FROM Creditor_Invoice_Expense WHERE (Creditor_Invoice_Expense.
FROM Job_Unfinished
WHERE (Major_Work_In_Progress = 0)
GROUP BY Account_Id, Job_Number
Which gives me the result of;
65 825.92 1240.45
652 86.10 NULL
652 49.08 NULL
652 NULL NULL
652 38.34 NULL
What I am trying to do is group the Account_ID's together as one total.
ie.
65 825.92 1240.45
652 173.52 NULL
If I omit 'Job_Number' from the GROUP BY statement it bemoans about 'Column 'Job_Unfinished.Job_Number
Anybody help me out or point me in the right direction.
With thanks, Mark
Quite new to the SQL Language and have been scratching my head with a group by with sub-select's summing some costs from another couple of tables.
Here is the statement;
SELECT Account_Id AS Expr1,
(SELECT SUM(TOTAL_COST) AS Expr2 FROM Job_Labour WHERE Job_Labour.JOB_NUMBER = Job_Unfinished.Job_Number)
(SELECT SUM(Amount) AS Expr3 FROM Creditor_Invoice_Expense WHERE (Creditor_Invoice_Expense.
FROM Job_Unfinished
WHERE (Major_Work_In_Progress = 0)
GROUP BY Account_Id, Job_Number
Which gives me the result of;
65 825.92 1240.45
652 86.10 NULL
652 49.08 NULL
652 NULL NULL
652 38.34 NULL
What I am trying to do is group the Account_ID's together as one total.
ie.
65 825.92 1240.45
652 173.52 NULL
If I omit 'Job_Number' from the GROUP BY statement it bemoans about 'Column 'Job_Unfinished.Job_Number
Anybody help me out or point me in the right direction.
With thanks, Mark
Kevin Cross
You can make your query a derived table and then sum on that grouped by just the accountid or rewrite query using left join instead of sub queries.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As LEFT JOINs:
SELECT ju.Account_Id,
, SUM(jl.TOTAL_COST) AS Labour
, SUM(cie.Amount) AS Material_Cost
FROM Job_Unfinished ju
LEFT JOIN Job_Labour jl ON jl.JOB_NUMBER = ju.Job_Number
LEFT JOIN Creditor_Invoice_Expense cie ON cie.Job_Number = ju.Job_Number
WHERE (ju.Major_Work_In_Progress = 0)
GROUP BY Account_Id
ASKER
Cheers, Thank you!