Aggregate Data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Aggregate Data

Author

Topic

udayfn12
Starting Member

15 Posts

Posted - 2004-03-16 : 17:36:06

Hello,

I need some help in developing a query on the following data. I need to aggregate the data on the codes. For any individual code I need to sum the value for that column pair. I have provided data, and output format below.

The same data is available in two different tables with different format. Query on the any of the table is fine.

Thanks in advance,
Reddy.

OutPut:
=====
Code : Count: Amount
01    4    2946548
03    1    1865
.
.
.
14    3    2036859
.
.
.
19    1    117481

Format1:
======
-- Create Table stmt:

Create table test1
(Cycle int, Docket varchar(4),
code1 float,
value1 Float,
code2 float,
value2 Float,
code3 float,
value3 Float
)

Data
====
Insert into test1
values (200309,'1111',1,5015,3,1865,7,266)
Insert into test1
values (200309,'1122',1,155216,14,409295,99,117481)
Insert into test1
values (200309,'1123',1,122347,10,761676,14,47136)
Insert into test1
values (200309,'1124',1,2663970,14,1580428,24,1997050)

==================================================
Format2:
======
create table test2
(cycle int, docket varchar(4),
linecode varchar(6), linevalue float)

insert into test2
values (200309,'1111','Code1',1)
insert into test2
values (200309,'1111','Value1',5015)
insert into test2
values (200309,'1111','Code2',3)
insert into test2
values (200309,'1111','Value2',1865)
insert into test2
values (200309,'1111','Code3',7)
insert into test2
values (200309,'1111','Value3',266)

insert into test2
values (200309,'1122','Code1',1)
insert into test2
values (200309,'1122','Value1',155216)
insert into test2
values (200309,'1122','Code2',14)
insert into test2
values (200309,'1122','Value2',409295)
insert into test2
values (200309,'1122','Code3',99)
insert into test2
values (200309,'1122','Value3',117481)

insert into test2
values (200309,'1123','Code1',1)
insert into test2
values (200309,'1123','Value1',122347)
insert into test2
values (200309,'1123','Code2',10)
insert into test2
values (200309,'1123','Value2',761676)
insert into test2
values (200309,'1123','Code3',14)
insert into test2
values (200309,'1123','Value3',47136)

insert into test2
values (200309,'1124','Code1',1)
insert into test2
values (200309,'1124','Value1',2663970)
insert into test2
values (200309,'1124','Code2',14)
insert into test2
values (200309,'1124','Value2',1580428)
insert into test2
values (200309,'1124','Code3',24)
insert into test2
values (200309,'1124','Value3',1997050)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-16 : 23:01:34

I chose Format 2 to provide a solution: It requires a lookup table which I have called lookup:


create table lookup ( code varchar(10),value varchar(10) )

insert into lookup
select 'Code1','Value1' union all
select 'Code2','Value2' union all
select 'Code3','Value3' 

select t.linevalue as code, count(t.linevalue) as [count], sum(t2.linevalue) [amount]
from test2 t
join lookup j on j.code = t.linecode
join test2 t2 on t2.linecode = j.value and t2.docket = t.docket
group by t.linevalue

drop table lookup

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources