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?

Aggr

Hi,

What I want is to to get SUM of col1 and list quarter data when
applicable.

DDL:
create table #temp (col1 int, rent int, transport int, qtr smallint,
other int);

DML:
insert into #temp
values(1, 800, 300, 1, 200)

insert into #temp
values(1, 800, 300, 2, 300)

insert into #temp
values(2, 800, 300, 2, 400)

Data retrieval DML:
select col1, sum(other) as other_Total, case when qtr = 1 then
sum(other) end qtr_total
from #temp
group by col1,qtr

Current Resultset:
col1 other_Total qtr_total
---- ---- ----
1 200 200
1 300 NULL
2 400 NULL

Desirable Resultset: (get ride of the middle row above and add up the
200 and 300), so, it would look like
col1 other_Total qtr_total
---- ---- ----
1 500 200
2 400 NULL

What am I missing here?

TIA.SELECT col1, SUM(other) AS other_total,
SUM(CASE WHEN qtr = 1 THEN other END) qtr_total
FROM #temp
GROUP BY col1

--
David Portas
SQL Server MVP
--|||Thank you, David.

Don

Agg op question

hi,

i have a table 'Details' with columns [id int; level varchar(20)]. i want to get two things from one single query:
1) the total number of items with Details.id=xxx
2) all the associated [level] text

i tried a few times doing things like:
SELECT [Level], COUNT(*) FROM Details WHERE id=xxx GROUP BY id

but it either gives me error "Column [Level] is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

i am using MS SQL Server as the back engine and T-SQL in Access...

Help~

thanks in advancePerhaps you can do this:

select level, (select count(*) from details where id=xxx) as cnt
from details where id=xxx;

Works in Oracle, I don't know about your DBMS.|||Beautifully done~ thanks!

Advice requested - How do I convert a Base36 number to SQL int

Hi,
Does anyone know if it is possible to create a Stored Procedure in
SQL7.0/2000 to do the following:
I need to convert an SQL int value to a Base36 value, and the other
direction Base36 to SQL int.
Example:
Take the (Base10) number 22, and convert to "M".
Take the (Base36) number "M" and convert to 22.
The Base36 digits are:
"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";
Thanks
Russell Mangel
Las Vegas, NVHi Russel
This is a solution for Hex which could be extended
http://www.umachandar.com/technical...ipts/Main89.htm
http://www.umachandar.com/technical...ipts/Main29.htm
For hex there is also an undocumented procedure xp_varbintohexstr
http://www.umachandar.com/technical...ipts/Main11.htm
John
"Russell Mangel" <russell@.tymer.net> wrote in message
news:ezmZ0qSEFHA.1396@.tk2msftngp13.phx.gbl...
> Hi,
> Does anyone know if it is possible to create a Stored Procedure in
> SQL7.0/2000 to do the following:
> I need to convert an SQL int value to a Base36 value, and the other
> direction Base36 to SQL int.
> Example:
> Take the (Base10) number 22, and convert to "M".
> Take the (Base36) number "M" and convert to 22.
> The Base36 digits are:
> "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";
> Thanks
> Russell Mangel
> Las Vegas, NV
>