Prev: Change short keyboard
Next: Stress testing
From: thomaspullen on 14 Jul 2010 10:05
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
From: thomaspullen on 14 Jul 2010 10:12
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')
From: Tom Cooper on 14 Jul 2010 16:03
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(a)hotmail.co.uk> wrote in message
news:56acb8ff-db36-4663-9523-3601143d70c3(a)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')

From: thomarse on 15 Jul 2010 00:19
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!
From: Tom Cooper on 15 Jul 2010 04:13
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(a)rm.com> wrote in message
news:007cb456-2a6e-445e-9809-70a364049b47(a)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!

 |  Next  |  Last
Pages: 1 2
Prev: Change short keyboard
Next: Stress testing