Get the number of keywords

Question

Suppose I have the data below, the last keyword is nothing more than a combination of the other 4 columns.

╔════╦══════╦════════════╦═════════╦════════════╦════════════════════════════════╗ ║ ID ║ Name ║ Add1 ║ Add2 ║ Add3 ║ Keyword ║ ╠════╬══════╬════════════╬═════════╬════════════╬════════════════════════════════╣ ║ 1 ║ John ║ W Brown St ║ Edison ║ Washington ║ JohnW Brown StEdisonWashington ║ ║ 2 ║ Paul ║ E High Rd ║ Peapack ║ New Jersey ║ PaulE High RdPeapackNew Jersey ║ ║ 3 ║ John ║ Greams Rd ║ Peapack ║ Washington ║ JohnGreams RdPeapackWashington ║ ╚════╩══════╩════════════╩═════════╩════════════╩════════════════════════════════╝ 

Now the user can enter any text field Name, Add1, Add2, Add3 and click on search, and he must search in all fields.

For this, I'm trying to do it,

 SELECT *, COUNT(ID) AS FREQUENCY FROM TABA WHERE ID IN ( (SELECT ID FROM TABA WHERE KEYWORD LIKE '%WASHINGTON%') UNION ALL (SELECT ID FROM TABA WHERE KEYWORD LIKE '%JOHN%') UNION ALL (SELECT ID FROM TABA WHERE KEYWORD LIKE '%PEAPACK%') ) GROUP BY ID ORDER BY FREQUENCY 

Expected Result:

 ╔════╦══════╦════════════╦═════════╦════════════╦═══════════╗ ║ ID ║ Name ║ Add1 ║ Add2 ║ Add3 ║ Frequency ║ ╠════╬══════╬════════════╬═════════╬════════════╬═══════════╣ ║ 3 ║ John ║ Greams Rd ║ Peapack ║ Washington ║ 3 ║ ║ 1 ║ John ║ W Brown St ║ Edison ║ Washington ║ 2 ║ ║ 2 ║ Paul ║ E High Rd ║ Peapack ║ New Jersey ║ 1 ║ ╚════╩══════╩════════════╩═════════╩════════════╩═══════════╝ 

But I get all frequency values ​​as 1. What am I doing wrong?

Answer 1

The part ... WHERE ID IN ( ...) ... will just check yes or no, but you want a count. This must be done using JOIN.

Try the following:

 SELECT TABA.*, COUNT(sub.ID) AS FREQUENCY FROM TABA JOIN ( (SELECT ID FROM TABA WHERE KEYWORD LIKE '%WASHINGTON%') UNION ALL (SELECT ID FROM TABA WHERE KEYWORD LIKE '%JOHN%') UNION ALL (SELECT ID FROM TABA WHERE KEYWORD LIKE '%PEAPACK%') ) sub ON sub.ID=TABA.ID GROUP BY TABA.ID ORDER BY FREQUENCY 

Answer 2

Why not make a simplified version without these subqueries:

 SELECT ID, Name, Add1, Add2, Add3, COUNT(*) AS FREQUENCY FROM TABA WHERE ID IN (SELECT ID FROM TABA WHERE KEYWORD LIKE '%WASHINGTON%' OR KEYWORD LIKE '%JOHN%' OR KEYWORD LIKE '%PEAPACK%') GROUP BY ID, Name, Add1, Add2, Add3 ORDER BY 6 

'Order by 6' means the sixth item in the selection area, which is the frequency

Answer 3

 SELECT TABA.*, sub.FREQUENCY FROM ( SELECT ID, COUNT(*) as FREQUENCY FROM ( (SELECT ID FROM TABA WHERE KEYWORD LIKE '%WASHINGTON%') UNION ALL (SELECT ID FROM TABA WHERE KEYWORD LIKE '%JOHN%') UNION ALL (SELECT ID FROM TABA WHERE KEYWORD LIKE '%PEAPACK%') ) a GROUP BY ID ) sub INNER JOIN TABA ON sub.ID=TABA.ID ORDER BY FREQUENCY DESC 

sql script