Re: Conditional computing

From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 06/18/04 

Date: Fri, 18 Jun 2004 20:09:38 +0530

hi eric,

It would have been more easier to give you solution, if you would have
posted sample table structure and data alongwith expected result set.

however on the basis of some information provided by you , the query given
in following example might be what you are looking for.

--sample data
create table tblInvestments (companyid int, sectorid int,
stateid int,
status varchar(10),
amount int)
go
create table company (companyid int primary key, companyname varchar(500))
go
create table sector(sectorid int primary key, sectorname varchar(500))
go
create table state(stateid int primary key, statename varchar(500))
go
insert into company values(1,'company1')
insert into company values(2,'company2')
insert into company values(3,'company3')
go
insert into sector values (1,'primary')
insert into sector values (2,'secondary')
insert into sector values (3,'manufacturing')
go
insert into state values (1,'CA')
insert into state values (2,'NJ')
insert into state values (3,'MA')
go
insert into tblinvestments values(1,1,1,'active',1000)
insert into tblinvestments values(1,1,1,'inactive',1000)
insert into tblinvestments values(2,1,1,'active',1000)
insert into tblinvestments values(1,2,1,'active',2000)
insert into tblinvestments values(2,2,1,'active',2000)
go
--required query
select b.companyname ,a.sectorname,
sum (case when c.companyid is null then 0 else 1 end ) 'investment' ,
sum(case c.status when 'active' then 1 else 0 end) 'active',
sum(case c.status when 'active' then c.amount else 0 end) 'total invested'
from sector a cross join company b
left outer join tblInvestments c
on a.sectorid = c.sectorid and b.companyid = c.companyid
group by a.sectorname, b.companyname
order by 1,2
compute sum (sum (case when c.companyid is null then 0 else 1 end )) by
b.companyname 

--
Vishal Parkar
vgparkar@yahoo.co.in | vgparkar@hotmail.com