SQL aggregate query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL aggregate query

Author

Topic

Maharishi
Starting Member

1 Post

Posted - 2012-02-04 : 08:34:20

Hello
I've transferred from MS Access to SQL server and am having trouble getting my head round how to best perform a particular query. Any help most appreciated.

Basically I have three tables

Table 1 rows each contain the name and details of a legal contract
CONTRACTID1, LAWYER1, CLIENT1, SITE1
CONTRACTID2, LAWYER2, CLIENT2, SITE2 ETC

Table 2 contains a list of payments received under each contract id and clause
CONTRACTID1, CLAUSE1, $5000
CONTRACTID1, CLAUSE2, $50000
CONTRACTID2, CLAUSE1, $100
CONTRACTID2, CLAUSE2, $2000 ETC

Table 3 contains a list of expenditure paid out under each contract id
CONTRACTID1, $400
CONTRACTID1, $600
CONTRACTID1, $1000
CONTRACTID1, $50
CONTRACTID2, $900

The trick is that payments and expenditure amounts are not related in any way.

All i want to do is create a query that calculates the sum of income grouped by contract from table 2 then calculates the sum of expenditure grouped by contract from table 3 and then joins this information to the contract details in table 1 so i get a table showing each contract and the total ins/outs.

Previously I achieved this by having two query objects in access and I have replicated by creating two views in sql server but i am looking for a more elegant solution of how to create a couple of separate nested subqueries but as i say i just cannot get my head round how to do it?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-04 : 08:44:01

You should be able to join all three tables together:

SELECT
	t1.ContractID,
	SUM(t2.PaymentsReceived) AS PaymentsReceived,
	SUM(t3.Expenditure) AS Expenditure
FROM
	Table1 t1
	LEFT JOIN Table2 t2 ON t1.ContractId = t2.ContractId
	LEFT JOIN Table3 t3 ON t1.ContractId = t2.ContractId
GROUP BY
	t1.ContractId;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-04 : 15:07:26

[code]
SELECT
t1.ContractID,t1.Lawyer,t1.client,t1.site,
COALESCE(t2.TotalPaymentsReceived,0) AS TotalPaymentsReceived,
COALESCE(t3.totalExpenditure,0) AS totalExpenditure
FROM
Table1 t1
LEFT JOIN (SELECT ContractId,SUM(PaymentsReceived) AS totalPaymentsReceived
FROM Table2
GROUP BY ContractId
)t2
ON t1.ContractId = t2.ContractId
LEFT JOIN (SELECT ContractId,SUM(Expenditure) AS totalExpenditure
FROM Table3
GROUP BY ContractId
)t3
ON t1.ContractId = t3.ContractId
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-04 : 15:13:13

using UNION instead of 2 left joins


SELECT
	t1.ContractID,t1.Lawyer,t1.client,t1.site,
	COALESCE(t2.TotalPaymentsReceived,0) AS TotalPaymentsReceived,
	COALESCE(t2.totalExpenditure,0) AS totalExpenditure
FROM	Table1 t1
	LEFT JOIN (SELECT ContractId,SUM(totalPaymentsReceived) AS totalPaymentsReceived,SUM(totalExpenditure) AS totalExpenditure
                   FROM
                   (
                   SELECT ContractId,SUM(PaymentsReceived) AS totalPaymentsReceived,CAST(0 AS Numeric(12,2))  AS totalExpenditure
                   FROM Table2 
                   GROUP BY ContractId
                   UNION ALL
                   SELECT ContractId,0,SUM(Expenditure)
                   FROM Table3 
                   GROUP BY ContractId
                   )t
                   GROUP BY ContractId
                  )t2 
        ON t1.ContractId = t2.ContractId

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources