Group By Question - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Group By Question

Author

Topic

IainMaclean
Starting Member

9 Posts

Posted - 2009-08-18 : 02:06:18

Hope you can help me. It has to work on SQL2000 for the next couple of months at least.

This query

Select Sum(remn_am)as Total_Remuneration, Jurisdiction, orgn_id as Organisation

From dbo.iamestab Establishments

Left Outer join dbo.Address Addresses
On establishments.residential_address_id = Addresses.adr_id

Left Outer Join dbo.tblQRegion PostCodeRegions
On ltrim(rtrim(addresses.post_cd)) = ltrim(rtrim(PostCodeRegions.post_code))

where orgn_id in (6818982,4934392,2694595,12672918)

Group By Orgn_id, Jurisdiction
Order By Orgn_id

Gives me the following results
Total_Remuneration, Jurisdiction, Organisation
9265700.00 1_MLBN 2694595
2025949.00 2_KNX 2694595
1694823.00 3_GLNG 2694595
1075561.00 4_BGO 2694595
2090586.00 5_SPN 2694595
1125198.00 6_ALBY 2694595
88497712.00 1_MLBN 4934392
4151006.00 2_KNX 4934392
5816064.00 3_GLNG 4934392
674358.00 4_BGO 4934392
278132.00 5_SPN 4934392
193039.00 6_ALBY 4934392
169007.00 7_MLDA 4934392
552855319.00 1_MLBN 6818982
131882373.00 2_KNX 6818982
99082310.00 3_GLNG 6818982
51079257.00 4_BGO 6818982
14736525.00 5_SPN 6818982
23351836.00 6_ALBY 6818982
9182427.00 7_MLDA 6818982
5544298.00 1_MLBN 12672918
71069211.00 2_KNX 12672918
57078878.00 3_GLNG 12672918
18182754.00 4_BGO 12672918
9244206.00 5_SPN 12672918
11160545.00 6_ALBY 12672918
113170405.00 7_MLDA 12672918

What i really want to be able to do is select only the Jurisdiction which has the highest Total_Remuneration. Eliminating duplicated organisations. i want to add to the query so i only retrieve these results.

9265700.00 1_MLBN 2694595
88497712.00 1_MLBN 4934392
552855319.00 1_MLBN 6818982
113170405.00 7_MLDA 12672918

I suspect i need to do a subquery of some kind. Any advice would be appreciated.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-18 : 02:20:52

SELECT MAX(Total_Remuneration) AS 'Total_Remuneration', Organisation
FROM TABLE GROUP BY Organisation

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-18 : 02:22:10

Select Sum(remn_am)as Total_Remuneration, Jurisdiction, orgn_id as Organisation
into #temp
From dbo.iamestab Establishments
Left Outer join dbo.Address Addresses
On establishments.residential_address_id = Addresses.adr_id
Left Outer Join dbo.tblQRegion PostCodeRegions
On ltrim(rtrim(addresses.post_cd)) = ltrim(rtrim(PostCodeRegions.post_code))
where orgn_id in (6818982,4934392,2694595,12672918)
Group By Orgn_id, Jurisdiction
Order By Orgn_id

select t.Total_Remuneration,t.Jurisdiction,t.Organisation
from #temp t
inner join (select max(Total_Remuneration) as Total_Remuneration ,Organisation from #temp) s on s.Total_Remuneration = t.Total_Remuneration and s.organisation = t.organisation

IainMaclean
Starting Member

9 Posts

Posted - 2009-08-18 : 02:51:14

I get this error

Warning: Null value is eliminated by an aggregate or other SET operation.

(27 row(s) affected)
Msg 8118, Level 16, State 1, Line 12
Column '#temp.Organisation' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-18 : 03:07:20

select t.Total_Remuneration,t.Jurisdiction,t.Organisation
from #temp t
inner join (select max(Total_Remuneration) as Total_Remuneration ,Organisation from #temp group by orgamisation) s on s.Total_Remuneration = t.Total_Remuneration and s.organisation = t.organisation

IainMaclean
Starting Member

9 Posts

Posted - 2009-08-18 : 03:24:28

Thanks bklr. works. Is there however a way i can do it purely in a query without creating a table?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-18 : 04:24:00

Welcome,
use the query instead of the table as subquery

IainMaclean
Starting Member

9 Posts

Posted - 2009-08-18 : 05:08:19

sorry for being a noob but how does one do that?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-18 : 05:20:51

[code]
select t.Total_Remuneration,t.Jurisdiction,t.Organisation
from (Select Sum(remn_am)as Total_Remuneration, Jurisdiction, orgn_id as Organisation
From dbo.iamestab Establishments
Left Outer join dbo.Address Addresses On establishments.residential_address_id = Addresses.adr_id
Left Outer Join dbo.tblQRegion PostCodeRegions On ltrim(rtrim(addresses.post_cd)) = ltrim(rtrim(PostCodeRegions.post_code))
where orgn_id in (6818982,4934392,2694595,12672918) Group By Orgn_id, Jurisdiction) t
inner join
(select max(Total_Remuneration) as Total_Remuneration ,Organisation
from (Select Sum(remn_am)as Total_Remuneration, Jurisdiction, orgn_id as Organisation
From dbo.iamestab Establishments
Left Outer join dbo.Address Addresses On establishments.residential_address_id = Addresses.adr_id
Left Outer Join dbo.tblQRegion PostCodeRegions On ltrim(rtrim(addresses.post_cd)) = ltrim(rtrim(PostCodeRegions.post_code))
where orgn_id in (6818982,4934392,2694595,12672918) Group By Orgn_id, Jurisdiction)k
group by orgamisation) s on s.Total_Remuneration = t.Total_Remuneration and s.organisation = t.organisation
[/code]

IainMaclean
Starting Member

9 Posts

Posted - 2009-08-18 : 19:41:35

Great thanks a lot.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-19 : 01:51:50

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources