Joins - 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 2005 Forums

Transact-SQL (2005)

Joins

Author

Topic

rob196
Starting Member

9 Posts

Posted - 2008-11-24 : 03:49:37

Hi,

I have 2 tables, i need to get the count of 1 column for each table and the sum of 1 column for each table, so 4 columns in total.

If i use a union i get the correct values back, but in 2 rows, the problem is i need it in 1 row.

Union version:
Select Count(Col5) As Number, Sum(Col6) As Val from Table1 Where Col5 = '8') UNION
Select Count(Col5) As Number, Sum(Col6) As Val from Table2 Where Col5 <> '8')

When i use join it gives me totally wrong values

Join Version:
Select Count(Table1.Col5) As Num1, Sum(Table1.Col6) As Val1, Count(Table2.Col5) As Num2, Sum(Table2.Col6) As Val2
From Table1
JOIN Table2
On Table1.Col10 = Table2.Col10 AND ((Table1.Col5 = '8')
Or Table2.Col5 <> '8'))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 03:54:49

select sum(number1), sum(val1), sum(number2), sum(val2)
from (
Select Count(Col5) As Number1, Sum(Col6) As Val1, 0 as number2, 0 as val2 from Table1 Where Col5 = '8'
UNION all
Select 0, 0, Count(Col5) As Number, Sum(Col6) As Val from Table2 Where Col5 <> '8'
) AS d

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 03:58:47

[code]Select Count(case when Col5 = '8' THEN Col5 ELSE NULL END) As Number1, Sum(case when Col5 = '8' THEN Col6 ELSE NULL END) As Val1 ,
Count(case when Col5 <> '8' THEN Col5 ELSE NULL END) As Number2, Sum(case when Col5 <> '8' THEN Col6 ELSE NULL END) As Val2
from Table1 [/code]

rob196
Starting Member

9 Posts

Posted - 2008-11-24 : 04:00:38

Thanks for the repiles,

Peso it works great, visakh16 ill give it a try

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 04:17:53

I don't think Visakh noticed that the four aggregations comes from two different tables.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 04:20:21

quote:
Originally posted by Peso

I don't think Visakh noticed that the four aggregations comes from two different tables.

E 12°55'05.63"
N 56°04'39.26"

yup...i missed that

thanks for the catch

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources