T-SQL query help - 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)

T-SQL query help

Author

Topic

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-16 : 18:32:37

Hi All,


create table #one (number int)
insert into #one 
select 1

create table #two (number int,a int,b int)
insert into #two
select 1,1,1
union all
select 1,1,1

create table #three (number int,c int,d int)
insert into #three
select 1,1,1
union all
select 1,1,1


SELECT #one.number, sum(#two.a) as SUM_A,sum(#two.b) as SUM_B, sum(#three.c)as SUM_C,sum(#three.d) as SUM_D
from #one
INNER JOIN #two ON #one.number=#two.number
INNER JOIN #three ON #one.number=#three.number
GROUP BY #one.number


DROP TABLE #one
DROP TABLE #two
DROP TABLE #three

Results are
number SUM_A SUM_B SUM_C SUM_D
----------- ----------- ----------- ----------- -----------
1 4 4 4 4

I want to get results
number SUM_A SUM_B SUM_C SUM_D
----------- ----------- ----------- ----------- -----------
1 2 2 2 2

Please help me.

Thanks

mk_garg

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-16 : 18:55:55

Thanks very much for giving the DDL and DML !!!! it is much appreciated.

unforunately, with the sample data and table/column names provided, I personally cannot figure out the logic behind returning those results. can you explain the logic behind your results and what you are looking for?

- Jeff

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-16 : 19:12:10

Hi Jeff,
I want to get sums of column #two.a, #two.b, #three.c,#three.d where #two.number and #three.number matches with #one.number

I hope i made bit clear. I remove some extra select statements as well.

Regards

mk_garg

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-16 : 19:47:36

break it down into parts:

select t1.number, t2.a, t2.b, t3.c, t3.d
from #one t1
inner join (select number, sum(a) as a, sum(b) as b from #two group by number) t2
on t1.number = t2.number
inner join (select number, sum(c) as c, sum(d) as d from #three group by number) t3
on t1.number = t3.number

you can only join the tables based on their relationships . you can't involve two tables togehter in a join if all of their primary keys aren't accounted for.

by the way -- no offense -- but that really is bad sample data. it has no meaning whatsoever and there are no primary keys in any of your tables. it is definitely appreciated that you put it out there, but it is hard to have any idea of what your needs really are with table names and column names like that, in addition to having no clear keys or relationships in your DDL.

but thanks for the effort

- Jeff

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-17 : 02:41:29

Thanks Jeff

mk_garg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources