x

Count Query

Table A and Table B; Each table has customerkey.

I need to find the following:
1. # of records with customerkey in both Table A and Table B
2. # of records with customerkey only in Table A NOT Table B
3. # of records with customerkey only in Table B NOT Table A

Thx

more ▼

asked Mar 29, 2011 at 11:09 AM in Default

avatar image

tredd
11 2 2 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Here is one way of doing it:

 select
     count(case when b.CustomerKey is null then 1 else null end) KeysOnlyInA,
     count(case when a.CustomerKey is null then 1 else null end) KeysOnlyInB,
     count(case when a.CustomerKey = b.CustomerKey then 1 else null end) InBoth
     from TableA a full join TableB b
         on a.CustomerKey = b.CustomerKey;

Here is how it works: when you issue a full join of the tables based on the CustomerKey then the results include

  • all records from A which have a match in B

  • records from A which don't have a match in B. For the latter records the value of CustomerKey is null.

  • records from B which don't have a match in A. For the latter records the value of CustomerKey is null.

So, when you count the records which exist in both tables, you use a.CustomerKey = b.CustomerKey condition because this is the base of your join. To count the keys missing in TableB you just count the records where b.CustomerKey is null, and to count the keys missing in TableA you count the records where a.CustomerKey is null.

Oleg

more ▼

answered Mar 29, 2011 at 11:36 AM

avatar image

Oleg
18.6k 3 7 28

@Oleg I haven't seen you answered the question...I have opened it for a while..I should have checked before clicking post:-)

Mar 29, 2011 at 11:48 AM DaniSQL

@DaniSQL Why? You offered a different approach which might end up to be more suitable. There is a small typo in your answer you might want to edit though (in the second query, your sub-select has FROM tableB a when the alias should be b not a. In other words, in should read FROM tableB b

Mar 29, 2011 at 11:56 AM Oleg

Worked perfectly in a single select. I got the same results with multiple selects but this is better using less code. Thx

Mar 29, 2011 at 12:06 PM tredd

@Oleg, I didn't realize we used a different approach....I just saw its answered and i was thinking it would have been better to keep the discussion in the first answer if the question is simple enough and answers are similar. Thanks for catching the typo I've just edited it.

Mar 29, 2011 at 12:07 PM DaniSQL

Nice. I would have gone with SUM(CASE WHEN... THEN 1 ELSE 0 END) rather than COUNT(CASE WHEN ... THEN 1 ELSE NULL END)... probably not as performant, though.

Mar 31, 2011 at 01:47 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

I hope you are not asking us to do your assignment:-)

 SELECT COUNT(*) FROM tableA a INNER JOIN tableB b
 ON a.CustomerKey=b.CustomerKey
         
 SELECT COUNT (*) FROM TableA a
 WHERE a.customerKey NOT IN (SELECT b.customerKey FROM tableB b)
 
 SELECT COUNT (*) FROM TableB B
 WHERE b.customerKey NOT IN (SELECT a.customerKey FROM tableA a)
more ▼

answered Mar 29, 2011 at 11:47 AM

avatar image

DaniSQL
4.9k 33 39 43

Thanks all! The second answer is actually the way I ran it prior to submitting the question. I was looking for a single select statement.

Thx

Mar 30, 2011 at 07:15 AM tredd

@tredd, Im glad you get what you wanted and Im sure you will comeback and hangout. BTW make sure you select @Oleg's answer if it helps you solve your problem.

Mar 31, 2011 at 12:56 PM DaniSQL
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2027
x1131

asked: Mar 29, 2011 at 11:09 AM

Seen: 950 times

Last Updated: Mar 31, 2011 at 01:33 PM

Copyright 2017 Redgate Software. Privacy Policy