Prev: Change short keyboardNext: Stress testing From: thomaspullen on 15 Jul 2010 05:44 Tom, yes I will do that shortly. I think I may need to use a CTE and OVER etc to partition the data then count by sector so I can do the maths. Essentially I need to sum all the trees for each sector to work out their density (trees/hectare), then sum all the trees for each (Species, Quality) combination and then work out the total trees/ hectare for each of these, probably by averaging the density for each of the sectors in which they're been counted in. I will post expected results when I've figured them out. Thank you for your time and help so far. Tom Pullen From: Iain Sharp on 15 Jul 2010 08:07 On Wed, 14 Jul 2010 07:05:02 -0700 (PDT), thomaspullen(a)hotmail.co.uk wrote: >--not right -- don't want to GROUP BY area, but how can I do this?!! >select t.Species, t.Quality, count(TreeId) as number, count(TreeId)/ >s.Area as 'NumberPerHectare' >from Trees t inner join >Sectors s >on t.SectorId = s.SectorId >group by t.Species, t.Quality, s.Area Hmm try this, (entirely untested) select t.Species, t.Quality, count(TreeId) as number, count(TreeId)/ y.Area as 'NumberPerHectare' from Trees t -- Subqueries to determine total area of sectors with these trees in. inner join -- Subquery x gets total area of all sectors containing trees/quals. (select x.species, x.quality, sum(x.Area) as Area from --Subquery y gets areas with these trees/qualities in them ( select distinct t.species, t.quality, t.area from trees t inner join Sectors s on t.SectorId = s.SectorId) as y group by y.species, y.quality ) as x on x.species = t.species and x.quality = t.quality group by t.Species, t.Quality Iain From: Thomas on 16 Jul 2010 08:39 I couldn't get that to work, eventually I ended re-writing it as select t.Species, t.Quality, count(TreeId) as number, count(TreeId)/ Area as 'NumberPerHectare' from Trees t inner join ( select y.Species, y.Quality, sum(y.Area) as Area from ( select distinct t.Species, t.Quality, S.Area from Trees t inner join Sectors s on t.SectorId = s.SectorId ) as y group by y.Species, y.Quality ) as x on x.Species = t.Species and x.Quality = t.Quality group by t.Species, t.Quality which is back to the same old error which prompted the post in the first place Msg 8120, Level 16, State 1, Line 1 Column 'x.Area' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. I am trying to get it to work using OVER .. PARTITION BY but it's addling my poor little brain. Will post back if I crack it. First  |  Prev  |  Pages: 1 2 Prev: Change short keyboardNext: Stress testing