Solved

SQL Group by with Summing in Sub-select

Posted on 2009-06-29
4
738 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
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.…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now