aggregate query help

--drop table bldg
--drop table room
create table bldg
(
bldg_id int not null primary key,
bldg_nm varchar(20)
)
go
create table room
(
bldg_id int not null,
room_num varchar(10) not null,
area numeric,
org varchar(10)
)
go
alter table room add constraint PKroom primary key (bldg_id, room_num)
go
insert into bldg values (1, 'B1')
insert into bldg values (2, 'B2')
insert into room values (1, '1RM1', 100.00, 'Org1')
insert into room values (1, '1RM2', 50.00, 'Org2')
insert into room values (1, '1RM3', 250.00, 'Org2')
insert into room values (2, '2RM1', 400.00, 'Org1')
insert into room values (2, '2RM2', 600.00, 'Org2')
go
select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
group by t1.bldg_nm, t2.org
order by t1.bldg_nm, t2.org
go
bldg org Area
-- -- ---
B1 Org1 100
B1 Org2 300
B2 Org1 400
B2 Org2 600
that works fine. however, i need two additional columns in the output.
one column is the total area of the building.
the other is the percentage of usage by the org of each building.
the output i need would look like this.
bldg org
Area Bldg Area Org Pct
-- -- ---
B1 Org1
100 400 25.00
B1 Org2
300 400 75.00
B2 Org1
400 1000 40.00
B2 Org2
600 1000 60.00
any way to do this in a select statement, i.e. no stored procedures or
cursors?Try something like this:
set nocount on
create table bldg
(
bldg_id int not null primary key,
bldg_nm varchar(20)
)
go
create table room
(
bldg_id int not null,
room_num varchar(10) not null,
area numeric,
org varchar(10)
)
go
alter table room add constraint PKroom primary key (bldg_id, room_num)
go
insert into bldg values (1, 'B1')
insert into bldg values (2, 'B2')
insert into room values (1, '1RM1', 100.00, 'Org1')
insert into room values (1, '1RM2', 50.00, 'Org2')
insert into room values (1, '1RM3', 250.00, 'Org2')
insert into room values (2, '2RM1', 400.00, 'Org1')
insert into room values (2, '2RM2', 600.00, 'Org2')
go
select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
group by t1.bldg_nm, t2.org
order by t1.bldg_nm, t2.org
go
select t1.bldg_nm, sum(t2.area) from bldg t1 join room t2
on t1.bldg_id = t2.bldg_id
group by t1.bldg_nm
go
select t3.bldg_nm as 'bldg', t4.org, Area, bldg_area , area / bldg_area *
100.00 PCT
from
(select t1.bldg_nm, sum(t2.area) bldg_area from bldg t1 join room t2
on t1.bldg_id = t2.bldg_id
group by t1.bldg_nm, t1.bldg_id) t3
join
(select t1.bldg_nm, t2.org, sum(t2.area) as 'Area'
from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
group by t1.bldg_nm, t2.org, t1.bldg_id) t4
on t3.bldg_nm = t4.bldg_nm
order by t3.bldg_nm, t4.org
drop table bldg
drop table room
--
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"ch" <ch@.dontemailme.com> wrote in message
news:4152E589.90B4DB41@.dontemailme.com...
> --drop table bldg
> --drop table room
> create table bldg
> (
> bldg_id int not null primary key,
> bldg_nm varchar(20)
> )
> go
> create table room
> (
> bldg_id int not null,
> room_num varchar(10) not null,
> area numeric,
> org varchar(10)
> )
> go
> alter table room add constraint PKroom primary key (bldg_id, room_num)
> go
> insert into bldg values (1, 'B1')
> insert into bldg values (2, 'B2')
> insert into room values (1, '1RM1', 100.00, 'Org1')
> insert into room values (1, '1RM2', 50.00, 'Org2')
> insert into room values (1, '1RM3', 250.00, 'Org2')
> insert into room values (2, '2RM1', 400.00, 'Org1')
> insert into room values (2, '2RM2', 600.00, 'Org2')
> go
>
> select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> group by t1.bldg_nm, t2.org
> order by t1.bldg_nm, t2.org
> go
> bldg org Area
> -- -- ---
> B1 Org1 100
> B1 Org2 300
> B2 Org1 400
> B2 Org2 600
>
> that works fine. however, i need two additional columns in the output.
> one column is the total area of the building.
> the other is the percentage of usage by the org of each building.
> the output i need would look like this.
> bldg org
> Area Bldg Area Org Pct
> -- -- ---
> B1 Org1
> 100 400 25.00
> B1 Org2
> 300 400 75.00
> B2 Org1
> 400 1000 40.00
> B2 Org2
> 600 1000 60.00
> any way to do this in a select statement, i.e. no stored procedures or
> cursors?|||thanks, although i had to change you inner join syntax to where clauses
because i don't care for the inner join syntax ;)
"Gregory A. Larsen" wrote:
> Try something like this:
> set nocount on
> create table bldg
> (
> bldg_id int not null primary key,
> bldg_nm varchar(20)
> )
> go
> create table room
> (
> bldg_id int not null,
> room_num varchar(10) not null,
> area numeric,
> org varchar(10)
> )
> go
> alter table room add constraint PKroom primary key (bldg_id, room_num)
> go
> insert into bldg values (1, 'B1')
> insert into bldg values (2, 'B2')
> insert into room values (1, '1RM1', 100.00, 'Org1')
> insert into room values (1, '1RM2', 50.00, 'Org2')
> insert into room values (1, '1RM3', 250.00, 'Org2')
> insert into room values (2, '2RM1', 400.00, 'Org1')
> insert into room values (2, '2RM2', 600.00, 'Org2')
> go
> select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> group by t1.bldg_nm, t2.org
> order by t1.bldg_nm, t2.org
> go
> select t1.bldg_nm, sum(t2.area) from bldg t1 join room t2
> on t1.bldg_id = t2.bldg_id
> group by t1.bldg_nm
> go
> select t3.bldg_nm as 'bldg', t4.org, Area, bldg_area , area / bldg_area *
> 100.00 PCT
> from
> (select t1.bldg_nm, sum(t2.area) bldg_area from bldg t1 join room t2
> on t1.bldg_id = t2.bldg_id
> group by t1.bldg_nm, t1.bldg_id) t3
> join
> (select t1.bldg_nm, t2.org, sum(t2.area) as 'Area'
> from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> group by t1.bldg_nm, t2.org, t1.bldg_id) t4
> on t3.bldg_nm = t4.bldg_nm
> order by t3.bldg_nm, t4.org
> drop table bldg
> drop table room
> --
> ----
> ----
> -
> Need SQL Server Examples check out my website
> http://www.geocities.com/sqlserverexamples
> "ch" <ch@.dontemailme.com> wrote in message
> news:4152E589.90B4DB41@.dontemailme.com...
> >
> > --drop table bldg
> > --drop table room
> >
> > create table bldg
> > (
> > bldg_id int not null primary key,
> > bldg_nm varchar(20)
> > )
> > go
> >
> > create table room
> > (
> > bldg_id int not null,
> > room_num varchar(10) not null,
> > area numeric,
> > org varchar(10)
> > )
> > go
> > alter table room add constraint PKroom primary key (bldg_id, room_num)
> > go
> >
> > insert into bldg values (1, 'B1')
> > insert into bldg values (2, 'B2')
> > insert into room values (1, '1RM1', 100.00, 'Org1')
> > insert into room values (1, '1RM2', 50.00, 'Org2')
> > insert into room values (1, '1RM3', 250.00, 'Org2')
> > insert into room values (2, '2RM1', 400.00, 'Org1')
> > insert into room values (2, '2RM2', 600.00, 'Org2')
> > go
> >
> >
> > select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> > from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> > group by t1.bldg_nm, t2.org
> > order by t1.bldg_nm, t2.org
> > go
> >
> > bldg org Area
> > -- -- ---
> > B1 Org1 100
> > B1 Org2 300
> > B2 Org1 400
> > B2 Org2 600
> >
> >
> > that works fine. however, i need two additional columns in the output.
> > one column is the total area of the building.
> > the other is the percentage of usage by the org of each building.
> > the output i need would look like this.
> >
> > bldg org
> > Area Bldg Area Org Pct
> > -- -- ---
> > B1 Org1
> > 100 400 25.00
> > B1 Org2
> > 300 400 75.00
> > B2 Org1
> > 400 1000 40.00
> > B2 Org2
> > 600 1000 60.00
> >
> > any way to do this in a select statement, i.e. no stored procedures or
> > cursors?|||thanks, although i had to change you inner join syntax to where clauses
because i don't care for the inner join syntax ;)
"Gregory A. Larsen" wrote:
> Try something like this:
> set nocount on
> create table bldg
> (
> bldg_id int not null primary key,
> bldg_nm varchar(20)
> )
> go
> create table room
> (
> bldg_id int not null,
> room_num varchar(10) not null,
> area numeric,
> org varchar(10)
> )
> go
> alter table room add constraint PKroom primary key (bldg_id, room_num)
> go
> insert into bldg values (1, 'B1')
> insert into bldg values (2, 'B2')
> insert into room values (1, '1RM1', 100.00, 'Org1')
> insert into room values (1, '1RM2', 50.00, 'Org2')
> insert into room values (1, '1RM3', 250.00, 'Org2')
> insert into room values (2, '2RM1', 400.00, 'Org1')
> insert into room values (2, '2RM2', 600.00, 'Org2')
> go
> select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> group by t1.bldg_nm, t2.org
> order by t1.bldg_nm, t2.org
> go
> select t1.bldg_nm, sum(t2.area) from bldg t1 join room t2
> on t1.bldg_id = t2.bldg_id
> group by t1.bldg_nm
> go
> select t3.bldg_nm as 'bldg', t4.org, Area, bldg_area , area / bldg_area *
> 100.00 PCT
> from
> (select t1.bldg_nm, sum(t2.area) bldg_area from bldg t1 join room t2
> on t1.bldg_id = t2.bldg_id
> group by t1.bldg_nm, t1.bldg_id) t3
> join
> (select t1.bldg_nm, t2.org, sum(t2.area) as 'Area'
> from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> group by t1.bldg_nm, t2.org, t1.bldg_id) t4
> on t3.bldg_nm = t4.bldg_nm
> order by t3.bldg_nm, t4.org
> drop table bldg
> drop table room
> --
> ----
> ----
> -
> Need SQL Server Examples check out my website
> http://www.geocities.com/sqlserverexamples
> "ch" <ch@.dontemailme.com> wrote in message
> news:4152E589.90B4DB41@.dontemailme.com...
> >
> > --drop table bldg
> > --drop table room
> >
> > create table bldg
> > (
> > bldg_id int not null primary key,
> > bldg_nm varchar(20)
> > )
> > go
> >
> > create table room
> > (
> > bldg_id int not null,
> > room_num varchar(10) not null,
> > area numeric,
> > org varchar(10)
> > )
> > go
> > alter table room add constraint PKroom primary key (bldg_id, room_num)
> > go
> >
> > insert into bldg values (1, 'B1')
> > insert into bldg values (2, 'B2')
> > insert into room values (1, '1RM1', 100.00, 'Org1')
> > insert into room values (1, '1RM2', 50.00, 'Org2')
> > insert into room values (1, '1RM3', 250.00, 'Org2')
> > insert into room values (2, '2RM1', 400.00, 'Org1')
> > insert into room values (2, '2RM2', 600.00, 'Org2')
> > go
> >
> >
> > select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> > from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> > group by t1.bldg_nm, t2.org
> > order by t1.bldg_nm, t2.org
> > go
> >
> > bldg org Area
> > -- -- ---
> > B1 Org1 100
> > B1 Org2 300
> > B2 Org1 400
> > B2 Org2 600
> >
> >
> > that works fine. however, i need two additional columns in the output.
> > one column is the total area of the building.
> > the other is the percentage of usage by the org of each building.
> > the output i need would look like this.
> >
> > bldg org
> > Area Bldg Area Org Pct
> > -- -- ---
> > B1 Org1
> > 100 400 25.00
> > B1 Org2
> > 300 400 75.00
> > B2 Org1
> > 400 1000 40.00
> > B2 Org2
> > 600 1000 60.00
> >
> > any way to do this in a select statement, i.e. no stored procedures or
> > cursors?|||> because i don't care for the inner join syntax ;)
That is a mistake IMO. The whole database world is moving away from the old join syntax. Of course
it is up to you, but perhaps time will come when you work in a project/organization that don't like
the old join syntax... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ch" <ch@.dontemailme.com> wrote in message news:41530D1C.953CF4F1@.dontemailme.com...
> thanks, although i had to change you inner join syntax to where clauses
> because i don't care for the inner join syntax ;)
>
> "Gregory A. Larsen" wrote:
> >
> > Try something like this:
> >
> > set nocount on
> >
> > create table bldg
> > (
> > bldg_id int not null primary key,
> > bldg_nm varchar(20)
> > )
> > go
> >
> > create table room
> > (
> > bldg_id int not null,
> > room_num varchar(10) not null,
> > area numeric,
> > org varchar(10)
> > )
> > go
> > alter table room add constraint PKroom primary key (bldg_id, room_num)
> > go
> >
> > insert into bldg values (1, 'B1')
> > insert into bldg values (2, 'B2')
> > insert into room values (1, '1RM1', 100.00, 'Org1')
> > insert into room values (1, '1RM2', 50.00, 'Org2')
> > insert into room values (1, '1RM3', 250.00, 'Org2')
> > insert into room values (2, '2RM1', 400.00, 'Org1')
> > insert into room values (2, '2RM2', 600.00, 'Org2')
> > go
> >
> > select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> > from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> > group by t1.bldg_nm, t2.org
> > order by t1.bldg_nm, t2.org
> > go
> >
> > select t1.bldg_nm, sum(t2.area) from bldg t1 join room t2
> > on t1.bldg_id = t2.bldg_id
> > group by t1.bldg_nm
> > go
> >
> > select t3.bldg_nm as 'bldg', t4.org, Area, bldg_area , area / bldg_area *
> > 100.00 PCT
> > from
> >
> > (select t1.bldg_nm, sum(t2.area) bldg_area from bldg t1 join room t2
> > on t1.bldg_id = t2.bldg_id
> > group by t1.bldg_nm, t1.bldg_id) t3
> >
> > join
> >
> > (select t1.bldg_nm, t2.org, sum(t2.area) as 'Area'
> > from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> > group by t1.bldg_nm, t2.org, t1.bldg_id) t4
> >
> > on t3.bldg_nm = t4.bldg_nm
> >
> > order by t3.bldg_nm, t4.org
> >
> > drop table bldg
> > drop table room
> >
> > --
> >
> > ----
> > ----
> > -
> >
> > Need SQL Server Examples check out my website
> > http://www.geocities.com/sqlserverexamples
> >
> > "ch" <ch@.dontemailme.com> wrote in message
> > news:4152E589.90B4DB41@.dontemailme.com...
> > >
> > > --drop table bldg
> > > --drop table room
> > >
> > > create table bldg
> > > (
> > > bldg_id int not null primary key,
> > > bldg_nm varchar(20)
> > > )
> > > go
> > >
> > > create table room
> > > (
> > > bldg_id int not null,
> > > room_num varchar(10) not null,
> > > area numeric,
> > > org varchar(10)
> > > )
> > > go
> > > alter table room add constraint PKroom primary key (bldg_id, room_num)
> > > go
> > >
> > > insert into bldg values (1, 'B1')
> > > insert into bldg values (2, 'B2')
> > > insert into room values (1, '1RM1', 100.00, 'Org1')
> > > insert into room values (1, '1RM2', 50.00, 'Org2')
> > > insert into room values (1, '1RM3', 250.00, 'Org2')
> > > insert into room values (2, '2RM1', 400.00, 'Org1')
> > > insert into room values (2, '2RM2', 600.00, 'Org2')
> > > go
> > >
> > >
> > > select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> > > from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> > > group by t1.bldg_nm, t2.org
> > > order by t1.bldg_nm, t2.org
> > > go
> > >
> > > bldg org Area
> > > -- -- ---
> > > B1 Org1 100
> > > B1 Org2 300
> > > B2 Org1 400
> > > B2 Org2 600
> > >
> > >
> > > that works fine. however, i need two additional columns in the output.
> > > one column is the total area of the building.
> > > the other is the percentage of usage by the org of each building.
> > > the output i need would look like this.
> > >
> > > bldg org
> > > Area Bldg Area Org Pct
> > > -- -- ---
> > > B1 Org1
> > > 100 400 25.00
> > > B1 Org2
> > > 300 400 75.00
> > > B2 Org1
> > > 400 1000 40.00
> > > B2 Org2
> > > 600 1000 60.00
> > >
> > > any way to do this in a select statement, i.e. no stored procedures or
> > > cursors?