Prev: Change short keyboard
Next: 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 keyboard
Next: Stress testing