Solved

SQL Group by with Summing in Sub-select

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query 4 46
SQL Union 20 44
email about the whoisactive result 7 18
Help Extract Specific in SQL 8 15
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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.
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

947 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

24 Experts available now in Live!

Get 1:1 Help Now