determine count in 2 tables and compare result

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)

determine count in 2 tables and compare result

Author

Topic

EmL
Starting Member

2 Posts

Posted - 2014-11-26 : 08:57:27

Hi Together,

since i'm not able to figure it out, I'll now give a try to solve my problem here ...

I have 2 tables

TableA/Field
Value1
Value2
Value3
Value2
Value1

TableB/Field
Value1
Value3
Value3

I want to know if it is possilble to count all identical values in T1 and join them and count with all identical values in T2, so that i would get something like this as a result

TA_COUNTA, TABLE_A , TA_COUNTB, TABLE_B
2, Value1, 1 , Value1
2, Value2, NULL, NULL
1, Value3, 2, Value3

Following is not working, because it sums up all rows and not only all A and all B

selecct count(ta.field), ta.field, count(tb.field), tb.field
from tableA ta
left join TableB tb
on ta.field = tb.field
group by ta.field, tb.field

Is this possible without using temporary tables within one sql command? Maybe there's a crack out there who can point me in the right direction ...

Thx
EmL

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-11-26 : 09:42:49

[code]
-- *** Test Data ***
CREATE TABLE #A(Field varchar(20) NOT NULL);
INSERT INTO #A VALUES ('Value1'),('Value2'),('Value3'),('Value2'),('Value1');
CREATE TABLE #B(Field varchar(20) NOT NULL);
INSERT INTO #B VALUES ('Value1'),('Value3'),('Value3');
-- *** End Test Data ***

WITH ACounts
AS
(
SELECT Field, COUNT(*) AS ACount
FROM #A
GROUP BY Field
)
,BCounts
AS
(
SELECT Field, COUNT(*) AS BCount
FROM #B
GROUP BY Field
)
SELECT *
FROM ACounts A
-- or FULL JOIN if #B contains values not in #A
LEFT JOIN BCounts B
ON A.Field = B.Field;
[/code]

EmL
Starting Member

2 Posts

Posted - 2014-11-26 : 10:42:20

Thx a lot Ifor! This solved my problem ... you are my hero!!! Until now i never used the WITH clause before, wich seems to be very interesting. I'll have that in my mind for the future.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources