getting the number of records with like values

I have a resultset that looks something like this:

Anzahl users_statdata_hobbies

499 Andere
266 Essen
60 Essen,Andere
127 Essen,Musik
10 Essen,Musik,Party,Andere
30 Essen,Party
4 Essen,Party,Andere
51 Kunst
4 Kunst,Andere
13 Kunst,Essen
4 Kunst,Essen,Andere

I get this with this query which might be altered somehow:
SELECT COUNT(*) AS Anzahl, users_statdata_hobbies
FROM vgetAuswertung2
GROUP BY users_statdata_hobbies
ORDER BY users_statdata_hobbies

Of course this is not normalized but I can't change this.

Nevertheless I need to get the full number of each Hobby and not only the combination of them.

So instead or in addition to the existing recordset I need e.g

357 Essen which ist the sum of all records containing 'Essen' in the above example

The list of individual hobbies is defined therefor I could loop through the list manually and search for 'WHERE Hobbies LIKE '%ESSEN%' and count but since it's quiet a big resultset and there are several other similar tasks already I'm looking for a more performant way and I'm sure it could be done in SQL directly.

Any ideas someone?

You could perform the initial select into a temp table, then count on that table.

For example:

SELECT into #temp COUNT(*) AS Anzahl, users_statdata_hobbies
FROM vgetAuswertung2
GROUP BY users_statdata_hobbies
ORDER BY users_statdata_hobbies

Select sum(Anzahl), substring(users_statdata_hobbies, 1, 5)

from #temp

group by substring(users_statdata_hobbies, 1, 5)

drop table #temp

You may need to play with conversion or cast on the first column if implicit conversion won't use it as an integer.

Martin

|||

I think your best bet is writing a Table-valued user-defined function that receives in two variables, a delimited list and the delimiter character. Then split the values (i.e. Essen,Musik,Party,Andere) into a returned table. You can then either insert all your results into a temp table and count or you can use relationships with your Master Hobby table to get your counts. The benifit is you get exact counts for each of the Hobbies, not just Essen, which in my mind is like planning ahead for what you might need later.

Good luck.

|||

Hello,

I would consider splitting up the Hobby table. You should create one record per Hobby. This will make selecting and joining those records MUCH more efficient. It might look like a lot of work at first, but such a design would also allow comparisons across languages if you think about multi-language websites later on. And it will also make it possible to extract a exact number of "matching hobbies" in a single querry.

Another "problem" with storing the hobbies in a string like that would be indexing.Also is "Wein,Weib,Gesang" the same as "Gesang,Wein,Weib"? Any querry using a "like '%bla%'" wont be able to use an index on the table. The result would be that you have to scan ALL records for every time someone is searching for a "match"... And since that is most likely one of the main functions of your site, you should try to keep it as efficient as possible.