CODEWITHSUNDEEP
mysqljoincountdistinct
hassan_i
hassan_i

Reputation: 331

Counting results of join on duplicate field

I have a table1 containing duplicate column value

Table1

id   code
1   201202  0000    1111
2   201202  0000    9999
3   201203  0000    9999
4   201203  0000    0999
5   201204  1000    1999
6   201204  2000    2999
7   201205  3000    3999
8   201205  4000    4999
9   201205  5000    5999

Table 2

id   numbers
1   2012020010  
2   2012024929  
3   2012033838
4   2012052434
5   2012052229
6   2012052232

I want to count all the numbers in table2 that are substring of distinct code in table 1 i.e. result should be

code   frequency
201202   2
201203   1
201205   3

I have been able to get all the numbers for every code but can't figure out how to count them

SELECT DISTINCT table1.code , table1.id, table2.number AS ph_npa, count( * )
FROM table1
INNER JOIN table2 ON substr( table2.number, 1, 6 ) = table1.code
GROUP BY table1.number

any help is appreciated.

Upvotes: 2

Views: 151

Answers (4)

brimble2010
brimble2010

Reputation: 18364

I'm not really one for using the "inner join" syntax and prefer to just use the cleaner looking implicit join on the data.

select
  count(*)
from
  npanxxsmall n, phone_numbers p
where
  substr(n.code, 1, 6) = substr(p.number, 1, 6);

Let me know if this works!

Upvotes: 1

hassan_i
hassan_i

Reputation: 331

ok I got it working and query is super fast

SELECT

DISTINCT A.code as code, B.Counts AS frequency FROM table1 AS A

INNER JOIN (

SELECT substr( number, 1, 6 ) AS subnumber, count( 1 ) AS Counts FROM table2 GROUP BY substr( number, 1, 6 ) ) AS B ON A.code = B.subnumber

i.e. select the number and frequency of number from table 2 and then join with distinct code form table 1

Upvotes: 0

KEINS
KEINS

Reputation: 71

Try out the following. It can have performance hits on large data sets, but will get you started. It is working with my test data.

SELECT SUBSTR(t2.numbers, 1,6) AS CODE, COUNT(*) AS frequency
FROM table_2 t2
WHERE SUBSTR(t2.numbers, 1,6) IN (SELECT t1.code FROM table_1 t1)
GROUP BY SUBSTR(t2.numbers, 1,6)

Let me know if its working!

Upvotes: 1

Robin Castlin
Robin Castlin

Reputation: 10996

SELECT t1.code, COUNT(*) AS frequency
FROM table_one AS t1
LEFT JOIN table_two AS t2
ON t2.numbers LIKE CONCAT(t1.code, '%')
GROUP BY t1.code

Either use LEFT JOIN or INNER JOIN depending on if you want rows with frequency = 0 or not. All I did was basicly to run a LIKE as join condition with the % wildcard.

Upvotes: 1

Related Questions