Joining Two Table - 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

General SQL Server Forums

New to SQL Server Programming

Joining Two Table

Author

Topic

winsonlee
Starting Member

4 Posts

Posted - 2009-08-24 : 03:59:33

Test1

Category ProductCode Customer SalesValue
Lights AP111 A 30.00
Lights AP111 B 21.00
Lights AP111 C 25.00
Furniture PP222 A 22.00
Furniture PP222 B 21.00
Lights AP112 A 55.00

Test 2

ProductCode FaultyValue
AP111 3.00
PP222 6.00
AP112 7.00

Result
Furniture 43.00 12.00
Lights 131.00 16.00

SELECT [Category],
sum([SalesValue]), sum(faultyValue)
FROM [sales].[dbo].[Test1], test2 where test1.productcode = test2.productcode group by category

The sales value is correct but the faultyvalue is incorrect. How do i change the sql statement to produce the following result ?

Result
Furniture 43.00 6.00
Lights 131.00 10.00

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-24 : 04:12:56

[code]DECLARE @test1 TABLE(Category varchar(10), ProductCode varchar(10), Customer char(1), SalesValue decimal(10,2))
INSERT INTO @test1
SELECT
'Lights' ,'AP111' ,'A' ,30.00 UNION ALL SELECT
'Lights' ,'AP111' ,'B' ,21.00 UNION ALL SELECT
'Lights' ,'AP111' ,'C' ,25.00 UNION ALL SELECT
'Furniture' ,'PP222' ,'A' ,22.00 UNION ALL SELECT
'Furniture' ,'PP222' ,'B' ,21.00 UNION ALL SELECT
'Lights' ,'AP112' ,'A' ,55.00

DECLARE @Test2 TABLE(ProductCode varchar(10), FaultyValue decimal(10,2))
INSERT INTO @test2
SELECT
'AP111' ,3.00 UNION ALL SELECT
'PP222' ,6.00 UNION ALL SELECT
'AP112' ,7.00

SELECT t1.category, SUM(t1.salesvalue), SUM(t2.faultyvalue)
FROM (
SELECT category, productcode, SUM(salesvalue) salesvalue
FROM @test1
GROUP BY category, productcode
)t1 join @test2 t2 on t1.productcode=t2.productcode
GROUP BY t1.category
[/code]
How you guys make color to it?

Hope can help...but advise to wait pros with confirmation...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-24 : 08:11:59

Hi waterduck,
whenever you see a formatted post and you want to know how it is to do you can click on "reply with quote".
There you can see and if you don't want to really reply you don't have to but you can see the codes...

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources