Select with Joins,Sums and group by Help!

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

SQL Server 2000 Forums

Transact-SQL (2000)

Select with Joins,Sums and group by Help!

Author

Topic

sd999
Starting Member

2 Posts

Posted - 2002-03-06 : 11:05:52

I am attempting to write Select statement to select the following information form a table

Table Def

Paysheet Table
Site,Order,Resource,Amount

I need to retrieve a sum of all orders for a particular resource
ie

select p.site_address,p.order_number,b.name,Sum(amount)
From PAYSHEET p
where p.Resource = 999

but i also as part of the same select would like to return a sum of amounts for the order and site regardless of resource
ie Select sum(amount) from paysheet where order = order being processed and site = site being processed

So i would get two amount returned from one select

Site,Order,Amount1,Amount2

where amount1 = Sum of amounts for this order,site and resource
where amount2 = sum of amounts for this order,site

I am also using a group by to return only one record for each site,order,resource.

Any one any ideas

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-03-06 : 11:34:26

have two select queries do the individual sumations and then join those on site

maybe something along the lines of



SELECT p1.Site, p1.Order, p1.Amount1, p2.Amount2
FROM 
(SELECT p.Site, p.Order, Sum(p.amount) as Amount1
FROM Paysheet p 
WHERE p.Resource = 999
GROUP BY p.Site, p.Order) AS p1
INNER JOIN 
(SELECT p2.Site, Sum(p2.Amount) as Amount2
FROM Paysheet p2
GROUP BY p2.Site) AS p2
ON (p1.Site = p2.Site)

sd999
Starting Member

2 Posts

Posted - 2002-03-06 : 11:54:21

Looks good have tried this

SELECT p1.Site_address, p1.Order_number, p1.Amount1, p2.Amount2
FROM
(SELECT ip.Site_address, ip.Order_number, Sum(ip.amount) as Amount1
FROM installer_Paysheet ip
WHERE ip.installer_id = 26554
GROUP BY ip.Site_address, ip.Order_number) AS p1

INNER JOIN
(SELECT p2.Site_address,p2.order_number, Sum(p2.Amount) as Amount2
FROM installer_Paysheet p2
GROUP BY p2.Site_address,p2.order_number) AS p2
ON (p1.Site_address = p2.Site_address)

Returns what i need thanks a lot,

Are there any downsides to using this statement ie on performance etc.

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-03-06 : 12:12:36

if you have your tables set up with appropriate indexs then you should be fine. YOu can check the query in Query Analyzer and watch the Execution Plan. If it is slow and not giving you the performance you want try running the Index Tuning Wizard.

ToddV
Posting Yak Master

218 Posts

Posted - 2002-03-06 : 12:38:51

quote:

Looks good have tried this

SELECT p1.Site_address, p1.Order_number, p1.Amount1, p2.Amount2
FROM
(SELECT ip.Site_address, ip.Order_number, Sum(ip.amount) as Amount1
FROM installer_Paysheet ip
WHERE ip.installer_id = 26554
GROUP BY ip.Site_address, ip.Order_number) AS p1

INNER JOIN
(SELECT p2.Site_address,p2.order_number, Sum(p2.Amount) as Amount2
FROM installer_Paysheet p2
GROUP BY p2.Site_address,p2.order_number) AS p2
ON (p1.Site_address = p2.Site_address)

This I believe this is equivalent and will likely perform a little better (Maybe not). Give it a shot.


SELECT p1.Site_address, 
	p1.Order_number, 
	SUM(CASE WHEN ip.installer_id = 26554  THEN Amount ELSE 0 END) AS Amount1, 
	SUM(Amount) AS p2.Amount2 
FROM installer_Paysheet
GROUP BY ip.Site_address, ip.Order_number

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources