groovymonkey
asked on
How to use the fields in one table to query items in another table to compare the number of occurences
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
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
ASKER
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
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
If you run my query, you will get what you are looking for.
ASKER
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?
select t1.lp,t1.t1_cnt,t2.t2_cnt,
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?
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
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;
Oh, I forgot you wanted the difference too!
add
(Sum(temptable.T1Count) - Sum(temptable.T2Count)) AS CountDifference
add
(Sum(temptable.T1Count) - Sum(temptable.T2Count)) AS CountDifference
I dont see any error in my query. Post your "syntax" error.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window