CODEWITHSUNDEEP
mysqlsql
PeteSE4
PeteSE4

Reputation: 327

Query to count number of times an id appears in string

Not even sure to word this, which is probably why I can't immediately think of a solution, however…

I have a table of store locations and a second table which are the stats which record, amongst other things, which stores have appeared in 'find my closest store' type searches.

So, for instance, table A has this content

ID | Store_Name
1  | London
2  | Edinburgh
3  | Bristol
4  | Crawley
5  | Brighton
6  | Cambridge

When a user does a search, resultant store ids are saved in the stats table like this (there are more columns, but keeping this simple)

ID | Search_Results
1  | 1,4,5,6

Which would indicate a visitor search had pulled up London, Crawley, Brighton and Cambridge in their results.

Now what I'm attempting to do is pull useful stats from this data and what to find the number of times each store had appeared in a search result. I played with this, but it obviously doesn't work (and I can see why, but you can probably see my confused thought process):

SELECT COUNT(ID) as Total, ID, Store_Name FROM a WHERE id IN (SELECT Search_Results FROM b) group by ID

What I'm trying to do is get a recordset that tells me the id of the store, the store name and the number of times it's appeared in a search result.

MySQL gurus, what do you think?

Upvotes: 0

Views: 430

Answers (3)

Dharmesh Patel
Dharmesh Patel

Reputation: 1891

Try this,

SELECT (SELECT count(t.`ID`) 
        FROM stats t 
        WHERE t.Search_Results REGEXP '[[:<:]]' | s.ID | '[[:>:]]') AS search_count,s.ID,s.Store_Name 
FROM store s

You can check this at following fiddle

Upvotes: 0

6ton
6ton

Reputation: 4214

One option I can think of is:

SELECT stores.id, stores.name, (SELECT count(*)
FROM stats
WHERE stats.search_results like '%' | stores.id | '%') hit_count
FROM stores

I don't have a database to try this, but I think this should work

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You have a very poor format for the stats table. You should have a second table for "StatsIds".

Here is the way to phrase your join:

SELECT COUNT(a.ID) as Total, a.ID, a.Store_Name
FROM a join
     b
     on find_in_set(a.id, b.Search_Results) > 0
group by a.ID;

Unfortunately, this query cannot be optimized using indexes.

Upvotes: 2

Related Questions