Hello everyone, I have a vexing problem that I am trying to solve
without resorting to something ugly like a cursor or a temp table....

(DDL and sample data below)

I have a table, Trees. Each tree has a TreeID (unique PK), a Species,
a Quality, and a related Sector. Each Sector in the Sectors table has
an Area in hectares.

I need a query to give me Species, Quality, Number of Trees, and
Number of Trees/Hectare for each (Species,Quality) combination.

Of course I can GROUP BY Species, Quality just to get the first three.
But I cannot get my head round how to join and get the area, and use
it to calculate the trees/unit area, without it being in the GROUP BY?
I am really trying to avoid having to resort to temp tables or worse
still a cursor. this mus be easy but I cannot see the wood for the
trees, ha ha! anyone care to point me up the right tree? (sorry, I'l
stop there)


create table Trees
(
TreeId int primary key
,SectorId int not null
,Species tinyint not null
,Quality char(1) not null
)

create table Sectors
(SectorId int primary key
,Area float not null
)

alter table Trees add constraint FK_1 foreign key (SectorId)
references Sector(SectorID)

create index idx_1 on Trees(SectorId)

--sample data:
insert into Sectors (SectorId, Area)
Values
,(1,5.9)
,(2,2.2)
,(3,3.2)
,(4,0.8)
,(5,3.5)

insert into Trees (TreeId, SectorId, Species, Quality)
values
(1,1,4,a)
,(2,2,3,b)
,(3,1,4,a)
,(4,1,4,b)
,(5,1,4,c)
,(6,1,5,a)
,(7,1,5,b)
,(8,1,5,a)
,(9,2,3,a)
,(10,2,3,b)
,(11,2,3,a)
,(12,2,3,b)
,(13,3,1,a)
,(14,3,1,a)
,(15,3,1,b)
,(16,3,1,c)
,(17,3,1,d)
,(18,3,1,a)
,(19,3,2,a)
,(20,3,2,b)
,(21,3,2,b)


--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

Sorry, corrected sample data for trees with quoted character data:-

insert into Trees (TreeId, SectorId, Species, Quality)
values
(1,1,4,'a')
,(2,2,3,'b')
,(3,1,4,'a')
,(4,1,4,'b')
,(5,1,4,'c')
,(6,1,5,'a')
,(7,1,5,'b')
,(8,1,5,'a')
,(9,2,3,'a')
,(10,2,3,'b')
,(11,2,3,'a')
,(12,2,3,'b')
,(13,3,1,'a')
,(14,3,1,'a')
,(15,3,1,'b')
,(16,3,1,'c')
,(17,3,1,'d')
,(18,3,1,'a')
,(19,3,2,'a')
,(20,3,2,'b')
,(21,3,2,'b')

If I understand what you want, just use the sum of the areas, that is

select t.Species, t.Quality, count(TreeId) as number,
  count(TreeId)/Sum(s.Area) as 'NumberPerHectare'
from Trees t inner join
Sectors s
on t.SectorId = s.SectorId
group by t.Species, t.Quality

Tom

<thomaspullen@hotmail.co.uk> wrote in message
news:56acb8ff-db36-4663-9523-3601143d70c3@j4g2000yqh.googlegroups.com...
> Sorry, corrected sample data for trees with quoted character data:-
>
> insert into Trees (TreeId, SectorId, Species, Quality)
> values
> (1,1,4,'a')
> ,(2,2,3,'b')
> ,(3,1,4,'a')
> ,(4,1,4,'b')
> ,(5,1,4,'c')
> ,(6,1,5,'a')
> ,(7,1,5,'b')
> ,(8,1,5,'a')
> ,(9,2,3,'a')
> ,(10,2,3,'b')
> ,(11,2,3,'a')
> ,(12,2,3,'b')
> ,(13,3,1,'a')
> ,(14,3,1,'a')
> ,(15,3,1,'b')
> ,(16,3,1,'c')
> ,(17,3,1,'d')
> ,(18,3,1,'a')
> ,(19,3,2,'a')
> ,(20,3,2,'b')
> ,(21,3,2,'b')

Thanks Tom, but that's the problem. I don't want to sum the area.

I have had another think about this and it may be that the count is
only ever for 1 sector so I can possibly divide by a constant instead
of a column value.

Thanks for responding anyhow!

Sorry I wasn't more help. Could you show the result you want from the query
given the sample data you provided? That should help us find the query you
are looking for.

Tom

"thomarse" <tpullen@rm.com> wrote in message
news:007cb456-2a6e-445e-9809-70a364049b47@5g2000yqz.googlegroups.com...
> Thanks Tom, but that's the problem. I don't want to sum the area.
>
> I have had another think about this and it may be that the count is
> only ever for 1 sector so I can possibly divide by a constant instead
> of a column value.
>
> Thanks for responding anyhow!

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

On Wed, 14 Jul 2010 07:05:02 -0700 (PDT), thomaspullen@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

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.