Solved

How to use the fields in one table to query items in another table to compare the number of occurences

Posted on 2011-03-17
8
196 Views
Last Modified: 2012-05-11
Hello,
I have two tables (table1 and table2) I need to compare fields between the tables
i.e. I need to determine the number of duplicates of table1.lp and the number of duplicates in table2.lp (that are equal to table1.lp values0 and calculate the difference

table1.lp          table2.lp   Diff
______________________________
5                         20           15
 
0
Comment
Question by:groovymonkey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 35160566
Are you looking for this?
select t1.lp,t1.t1_cnt,t2.t2_cnt,t2.t2_cnt - t1.t1_cnt as diff
  from (select lp,count(*) t1_cnt from table1 group by lp) t1
  join (select lp,count(*) t2_cnt from table1 group by lp) t2 
    on t1.lp = t2.lp

Open in new window

0
 

Author Comment

by:groovymonkey
ID: 35161292
haha I am not sure...to clarify...I was in  a rush when posting the question....

I have one table that I know is full of duplicate records and I want to do several things....I need to...

1.  do a duplicate count for this table (table1)
2. and then query only the lp values in table 2 that are in table 1
3. and do a duplicate count for these values

for example lets say in table1 I have the following records

lp
ddd123
ddd123
ddd123
eee555
eee555

and in table2 I have the following records

lp
ddd123
ddd123
ddd123
ddd123
ddd123
www222
www222
eee555
eee555
eee555
eee555

I want the following output

lpcommon       table1sum       table2sum

ddd123                 3                    5
eee555                 2                    4

thanks
groovymonkey

0
 
LVL 41

Expert Comment

by:Sharath
ID: 35161298
If you run my query, you will get what you are looking for.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:groovymonkey
ID: 35164891
I am running this and I am getting a syntax error message

select t1.lp,t1.t1_cnt,t2.t2_cnt,t2.t2_cnt - t1.t1_cnt as diff
  from (select lp,count(*) t1_cnt from t1 group by lp) t1
  join (select lp,count(*) t2_cnt from t1 group by lp) t2
    on t1.lp = t2.lp

Do you know where the error is?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35167111
Hakey Shakey Mang!
You're looking for an ugly little subquery Union All temptable Group by query!
That any askin' for much

First build your conditon.
You want only lp values that are in t1
SELECT lp FROM t1
That will be the subquery

Now you want the values of lp in t2 that are in t1
SELECT lp FROM t2 HAVING t2.lp in (SELECT lp FROM t1)

Now you want counts!
Thats where the union all is going to in

SELECT lp, Count(t1.lp) as T1Count, 0 as T2Count  from t1 GROUP BY t1.lp
UNION ALL
SELECT lp, T1Count as 0, Count(t2.lp) as T2Count FROM t2 GROUP BY t2.lp HAVING t2.lp in (SELECT lp from t1)

Now you have two records for each lp.
Some value and 0
0 and some value.
Group and sum them and you're there.
That's the temptable.

The earlier mess becomes the temptable and you select, sum and group from it

SELECT temptable.lp, Sum(temptable.T1Count) As T1FinalCount, Sum(temptable.T2Count) as T2FinalCount FROM(

SELECT lp, Count(t1.lp) as T1Count, 0 as T2Count  from t1 GROUP BY t1.lp
UNION ALL
SELECT lp, T1Count as 0, Count(t2.lp) as T2Count FROM t2 GROUP BY t2.lp HAVING t2.lp in (SELECT lp from t1)

) as temptable

GROUP BY temptable.lp;

Child's play!

Take the SQL from the code window
Using the Query Editor, one step at a time, you can get there
SELECT temptable.lp, Sum(temptable.T1Count) AS T1FinalCount, Sum(temptable.T2Count) AS T2FinalCount
FROM [SELECT t2.lp, Count(t2.lp) AS T2Count, 0 AS T1Count
FROM t2
GROUP BY t2.lp
HAVING (((t2.lp) In (SELECT t1.lp
FROM t1
)))


Union All

SELECT t1.lp, 0 as  T2Count, Count(t1.lp) AS T1Count
FROM t1
GROUP BY t1.lp]. AS temptable
GROUP BY temptable.lp;

Open in new window

0
 
LVL 26

Expert Comment

by:Nick67
ID: 35167135
Oh, I forgot you wanted the difference too!
add
(Sum(temptable.T1Count) - Sum(temptable.T2Count)) AS CountDifference
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35168370
I dont see any error in my query. Post your "syntax" error.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 35168755
@Sharath_123

Your syntax is fairly bare.  The Access 2003 query editor certainly won't allow the skipping of the AS keyword.
'count(*) t1_cnt'
'count(*) t2_cnt'
If you don't give a calculated column a name in the Query by Example, Access will invent one to use with AS.
But you have to have one
I tried it.

Also the original post asked for a duplicate count for t1.  Period.
You have a join on t1.lp = t2.lp
Not a left or right join.
So he won't get a count or difference for t1.lp values that have no match in t2 in any event.

I've never seen syntax like yours.  Is it coming from the query editor in Access?
The way you use JOIN--I do that in the SQL Server Management Express-- but I didn't know that Access will join two temptables that way.
It won't join them with JOIN
It will with INNER JOIN -- although that doesn't give the OP's requirements.

This would work in the query editor

Select temp2.lp, cnt1, cnt2,  cnt2 - nz(cnt1,0) as diff from
(Select t2.lp, Count(t2.lp) as cnt1 from t2 group by  t2.lp) as temp1
right Join
(Select t1.lp, Count(t1.lp) as cnt2 from t1 group by t1.lp ) as temp2
on temp1.lp =temp2.lp

and is simpler than my original post


Select temp2.lp, cnt1, cnt2,  cnt2 - nz(cnt1,0) as diff from
(Select t2.lp, Count(t2.lp) as cnt1 from t2 group by  t2.lp) as temp1
right Join
(Select t1.lp, Count(t1.lp) as cnt2 from t1 group by t1.lp ) as temp2
on temp1.lp =temp2.lp

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question