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]
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -