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.
ers, thanks.|||i find it burdensom and unnecessary, in recreating a simplified version
u_r_twisted wrote:
Gosh, I HATE identity columns. They are a pain in the rear to work with.... Nice for some things, but when doing ETL they stink. When you look at the link at my site above (ssistalk.com) that John linked to, I have a small discussion on why using identity columns is not preferred.