Solved

SQL Group by with Summing in Sub-select

Posted on 2009-06-29
4
748 Views
Last Modified: 2012-05-07
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) AS Labour,
(SELECT SUM(Amount) AS Expr3 FROM Creditor_Invoice_Expense WHERE (Creditor_Invoice_Expense.Job_Number = Job_Unfinished.Job_Number)) AS Material_Cost

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' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.' of course.

Anybody help me out or point me in the right direction.

With thanks, Mark

0
Comment
Question by:ozbailer
  • 3
4 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24742319
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.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 24742325
Using what you have as a derived table:
SELECT Account_Id
, SUM(Labour) AS Labour
, SUM(Material_Cost) AS Material_Cost
FROM (SELECT     Account_Id, 
 
(SELECT SUM(TOTAL_COST) AS Expr2 FROM Job_Labour WHERE Job_Labour.JOB_NUMBER = Job_Unfinished.Job_Number) AS Labour,
(SELECT SUM(Amount) AS Expr3 FROM Creditor_Invoice_Expense WHERE (Creditor_Invoice_Expense.Job_Number = Job_Unfinished.Job_Number)) AS Material_Cost
 
FROM  Job_Unfinished
 
WHERE (Major_Work_In_Progress = 0)
 
GROUP BY Account_Id, Job_Number
) t
GROUP BY Account_Id

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24742336
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

Open in new window

0
 

Author Closing Comment

by:ozbailer
ID: 31598225
Cheers, Thank you!
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question