Problems with nested Query - 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 2012 Forums

Transact-SQL (2012)

Problems with nested Query

Author

Topic

Hrungdak
Starting Member

2 Posts

Posted - 2013-09-17 : 09:35:02

Hello,

I have two tables:

Table1:
--------------------------------
Art ID Count1
123 1 10
123 2 34
123 3 13
234 1 1
345 1 30
345 2 20

Table2:
---------------------------------
Art ID Count2
123 1 8
123 2 15
345 1 50

I need a result in this manner:

Result
-------------------------------------------------------------------
Art Count1 Count2
123 57 23
234 1 0
345 50 50

I had many attempts with SUM and GROUP BY and JOINS, but I dont get it right. Can anyone help me here?

Alex

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-17 : 10:11:01

Here is one way.

SELECT
	COALESCE(a.art,b.art) AS art,
	SUM(count1) AS count1,
	SUM(count2) AS count2
FROM
	Table1 a
	FULL JOIN Table2 b ON
		a.art = b.art
GROUP BY
	COALESCE(a.art,b.art);

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-17 : 10:11:12

select isnull(t1.art,t2.art) as art,
isnull(count1, 0) as count1,
isnull(count2, 0) as count2
from
(
select art, sum(count1) as Count1
from table1
group by art
)t1
full join
(
select art, sum(count2) as Count2
from table2
group by art
)t2
on t1.art = t2.art

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-09-17 : 21:25:21

[code]
SELECT Art, Count1 = SUM(Count1), Count2 = SUM(Count2)
FROM
(
SELECT Art, Count1, Count2 = 0 FROM TABLE1 UNION ALL
SELECT Art, Count1 = 0, Count2 FROM TABLE2
) d
GROUP BY Art
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Hrungdak
Starting Member

2 Posts

Posted - 2013-09-18 : 00:49:23

Result of James K:
art count1 count2
123 114 69
234 1
345 50 100

Count1 is multiplied by the number of records in table 2. Same problem as i had.

Result of Sigmas:
art count1 count2
123 57 23
234 1 0
345 50 50

Looks perfect to me.

Result of khtan:
art count1 count2
123 57 23
234 1 0
345 50 50

Also perfect.

Thanks a lot. You were a great help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources