Do Count for each row of a table

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)

Do Count for each row of a table

Author

Topic

Jarhead104
Starting Member

10 Posts

Posted - 2009-10-29 : 13:47:20

Hi!

I have the following tables:

Table 1
A 0
B 0
C 0
D 1
E 0
...

Table 2
A 1
A 2
A 3
A 5
D 3
D 6
D 9
E 2
...

I now want to get a table with
A <number>
B <number>
C <number>
D <number>
E <number>
...

where <number> should be the result of
Select column 2 where column 1 = A
minus
Select count (column 2 of table 2) where column 1 = A
Result should be: -4

Select count (column 2 of table 2) where column 1 = B
minus
Select column 2 where column 1 = B
Result should be: 0

Select count (column 2 of table 2) where column 1 = D
minus
Select column 2 where column 1 = D
Result should be: -2

and so on...

Can anyone give me a hint or an example? I'm totally new to this...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-29 : 14:23:10

If I understand you correctly, this may not be the best way but it does work.

Jim

DECLARE @Table1 Table (Col1 char(10),Col2 int)
INSERT INTO @Table1
SELECT 'A', 0 UNION ALL
SELECT 'B', 0 UNION ALL
SELECT 'C', 0 UNION ALL
SELECT 'D', 1 UNION ALL
SELECT 'E', 0

--------------------------------------------------------------------------------

DECLARE @Table2 Table (Col1 char(10),Col2 int)
INSERT INTO @Table2
SELECT 'A', 1 UNION ALL
SELECT 'A', 2 UNION ALL
SELECT 'A', 3 UNION ALL
SELECT 'A', 5 UNION ALL
SELECT 'D', 3 UNION ALL
SELECT 'D', 6 UNION ALL
SELECT 'D', 9 UNION ALL
SELECT 'E', 2

SELECT t1.Col1
,[Number] = t1.Col2 - count(t2.Col2)
from
@table1 t1
left join
@table2 t2
on
t1.col1 = t2.col1
group by t1.col1,t1.col2

Everyday I learn something that somebody else already knew

Jarhead104
Starting Member

10 Posts

Posted - 2009-11-06 : 10:24:19

Thank you, jimf.

Works great! It took a little to use this on my original database but it worked finally :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources