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.

getting table counts

I want to get a resultset of every table in the database, with the
current record count of each. What is the easiest way to do this?

I can get the list of tables with:

Select s.name from sysobjects s where xtype = 'U'

each s.name is a table name, but I'm not sure how to join a record count
column to the resultset.

Thanks,
RickNAssuming your statistics are up to date you can use

SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID('<table_name>') AND indid < 2

This will perform better than

SELECT COUNT(*) from <table_name
This info is from http://www.sql-server-performance.com/

You could use a cursor to loop through the list of tables and stuff the
counts into a temp table. Perhaps someone else will have a way to do this
without a cursor.

Hope this helps,

CJ

"Rick" <rick@.abasoftware.com> wrote in message
news:28d7cbb9.0309231030.13e8f503@.posting.google.c om...
> I want to get a resultset of every table in the database, with the
> current record count of each. What is the easiest way to do this?
> I can get the list of tables with:
> Select s.name from sysobjects s where xtype = 'U'
> each s.name is a table name, but I'm not sure how to join a record count
> column to the resultset.
> Thanks,
> RickN|||So this would be the non-cursor solution:

select o.name, i.rows
from sysobjects o, sysindexes i
where i.id = OBJECT_ID(o.name)
and i.indid = 0

Shervin

"CJ" <chris@.hrn.org> wrote in message news:bkqdqr$34q$1@.reader2.nmix.net...
> Assuming your statistics are up to date you can use
> SELECT rows
> FROM sysindexes
> WHERE id = OBJECT_ID('<table_name>') AND indid < 2
> This will perform better than
> SELECT COUNT(*) from <table_name>
> This info is from http://www.sql-server-performance.com/
> You could use a cursor to loop through the list of tables and stuff the
> counts into a temp table. Perhaps someone else will have a way to do this
> without a cursor.
> Hope this helps,
> CJ
>
> "Rick" <rick@.abasoftware.com> wrote in message
> news:28d7cbb9.0309231030.13e8f503@.posting.google.c om...
> > I want to get a resultset of every table in the database, with the
> > current record count of each. What is the easiest way to do this?
> > I can get the list of tables with:
> > Select s.name from sysobjects s where xtype = 'U'
> > each s.name is a table name, but I'm not sure how to join a record count
> > column to the resultset.
> > Thanks,
> > RickN|||Rick (rick@.abasoftware.com) writes:
> I want to get a resultset of every table in the database, with the
> current record count of each. What is the easiest way to do this?
> I can get the list of tables with:
> Select s.name from sysobjects s where xtype = 'U'
> each s.name is a table name, but I'm not sure how to join a record count
> column to the resultset.

SELECT 'SELECT ''name'', COUNT(*) FROM ' + name
FROM sysobjects
WHERE xtype = 'U'
AND objectproperty(id, 'IsMSShipped') = 1
ORDER BY name

Cut and paste.

If you want to run it unattended, you can use the stored procedure
sp_MSforeachtable:

EXEC sp_MSforeachtable 'SELECT ''?'', COUNT(*) FROM ?'

Note that this procedure is undocumetned.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks everyone for the good ideas.
I've implemented the following and it gets me exactly what I need.

select o.name, i.rows
from sysobjects o, sysindexes i
where i.id = OBJECT_ID(o.name)
and i.indid < 2 and o.xtype = 'u'

RickN

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!