Solved

Classic ASP - group multiple keywords in database by popularity

Posted on 2010-09-10
37
527 Views
Last Modified: 2012-05-10
My database has entries "john doe" "john" "chicago" "new york" "new hampshire" in table "keywords". Each entry in it's own row.
I want to display these entries by popularity like:

2x john
2x new
1x doe
1x chicago
1x york
1x hampshire

How to do it? Thanks
0
Comment
Question by:matija_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 11
  • 7
  • +2
37 Comments
 
LVL 3

Expert Comment

by:FreakyEddie
ID: 33646744
Is the devider a space? i.e. Should an entry with two words, with a space in between, be seen as two seperate keywords?

Do you want it to be a solution in SQL or in ASP?
0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 33646750
I would probably use the TOP 100 or something like that and then sum them by number of Occurrance.

For instance, let's say that you have a field (and I think you should) called Occurrance on your table.

Then during insert statement, insert initial record, and then increment the count anytime same name is inserted, you increment like:

DIM intCount
intCount = objRS("Occurrance")
objRS("Occurrance") = intCount + 1

SQL = "SELECT TOP 100 name, SUM(Occurrance) as Occurrance " & _
"FROM YourTable & _
"GROUP BY name " & _
"ORDER BY SUM(Occurrance) DESC"

Then that will give you the result.

You will use your UI to present it as 2x John, etc

Hope this helps
0
 
LVL 6

Author Comment

by:matija_
ID: 33646797
@FreakyEddie
Yes, divider is space and entry with two or more words should be seen as separate keywords. It doesn't matter whether solution is in VbScript or SQL query.

@sammySeltzer
Thanks for suggestion, but there are several thousands of records already written to database, and now I'm trying to deal with that.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 13

Expert Comment

by:Surone1
ID: 33646836
if you write all the keywords seperately to another table first it makes things much easier.
you coud then "select distinct keyword, count(keyword) from table group by keyword order by keyword desc"
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33646879
"select distinct keyword, count(keyword) as mycount from table group by keyword order by keyword desc"
0
 
LVL 6

Author Comment

by:matija_
ID: 33646908
@Surone1
I am aware of that, thanks, but as I wrote, keywords are already written.

So far I've managed to pull out all keywords from database, separate them by space, place in array, but I cannot count occurances in that array :( Hence my question, there might be an easier way of doing this, while preserving the database structure and records already written.
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33646923
use a temporary table?
0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 33646932
I think the concept is the same.

SQL = "SELECT count(name) as Occurrance " & _
"FROM YourTable & _
"GROUP BY name " & _
"ORDER BY count(name) DESC"

Then on the asp side of things, you can do

do while not rs.EOF
   fname= rs("name")
    else
     response.write "no name found"
  end if
 
      Response.Write"<Font size=""2""> x " & rs("fname") & " </font>"
rs.MoveNext
loop
rs.Close
set rs = nothing
objConn.close
set objConn = nothing


something like this.

not tested of course

0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 33646942
sorry put <br> at end
Response.Write"<Font size=""2""> x " & rs("fname") & " </font><BR>"
0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 33647016
again, another small mistake.

Remove the
  else
     response.write "no name found"
  end if
0
 
LVL 6

Author Comment

by:matija_
ID: 33647022
@sammySeltzer
You've missed the point, I know how to code such simple script to count and group identical entries and display them, but I need the entries to be divided into separate keywords first and then count and group them.
I don't want to make new tables or anything. I wrote snippet that separates the keywords and put them all in one giant Array, but I'm stuck how to group them, hence the original question.
0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 33647141
ok got it.
0
 
LVL 3

Expert Comment

by:FreakyEddie
ID: 33647166
What's the max numbers of spaces you have within one field? Two, three or ten.

With two or three we can do it in a query, with 10 the query is gonna be a bit long.
0
 
LVL 6

Author Comment

by:matija_
ID: 33647183
@FreakyEddie
Lets try with 3 spaces, I can easily modify the script if more than that. There is not limit in working example.
0
 
LVL 3

Expert Comment

by:FreakyEddie
ID: 33647219
i'll do my best
0
 
LVL 3

Accepted Solution

by:
FreakyEddie earned 500 total points
ID: 33647969
Got it for 2 keywords

To explain it a bit:

CHARINDEX(' ', strSingleKeyword) + 1, field3) = field4
CHARINDEX(' ', strSingleKeyword, fiel2 – fiel1) = field3
CHARINDEX(' ', strSingleKeyword) + 1) = field2
CHARINDEX(' ', strSingleKeyword) = field1

Hope you can get it for 3... If I have it I'll let you know
SELECT COUNT(*), field1 FROM
(

SELECT     SUBSTRING(strSingleKeyword, 0, CHARINDEX(' ', strSingleKeyword)) AS field1
FROM         tableKeywords
UNION ALL
SELECT     LTRIM(RTRIM(SUBSTRING(strSingleKeyword, CHARINDEX(' ', strSingleKeyword) + 1, CHARINDEX(' ', strSingleKeyword, CHARINDEX(' ', strSingleKeyword) + 1) - CHARINDEX(' ', strSingleKeyword)))) 
                      AS field1
FROM         (SELECT     LTRIM(RTRIM(strSingleKeyword)) AS strSingleKeyword
                       FROM          tableKeywords AS der_table1) AS derivedtbl_1
WHERE     (strSingleKeyword LIKE '%  %')
)
group by field1
ORDER BY field1

Open in new window

0
 
LVL 3

Expert Comment

by:FreakyEddie
ID: 33648188
This should do the trick for 3 keywords.
SELECT     COUNT(*) AS Expr1, field1
FROM         (SELECT     field1
                       FROM          (SELECT     SUBSTRING(strSingleKeyword, 0, CHARINDEX(' ', strSingleKeyword)) AS field1
                                               FROM          tableKeywords
                                               UNION ALL
                                               SELECT     LTRIM(RTRIM(SUBSTRING(strSingleKeyword, CHARINDEX(' ', strSingleKeyword) + 1, CHARINDEX(' ', strSingleKeyword, CHARINDEX(' ', strSingleKeyword) + 1) 
                                                                     - CHARINDEX(' ', strSingleKeyword)))) AS field1
                                               FROM         (SELECT     LTRIM(RTRIM(strSingleKeyword)) AS strSingleKeyword
                                                                      FROM          tableKeywords AS der_table1) AS derivedtbl_1
                                               WHERE     (strSingleKeyword LIKE '%  %')) AS derived_table3
                       UNION ALL
                       SELECT     LTRIM(RTRIM(SUBSTRING(strSingleKeyword, CHARINDEX(' ', strSingleKeyword, CHARINDEX(' ', strSingleKeyword) + 1) + 1, CHARINDEX(' ', strSingleKeyword, CHARINDEX(' ', 
                                             strSingleKeyword, CHARINDEX(' ', strSingleKeyword) + 1) + 1) - CHARINDEX(' ', strSingleKeyword, CHARINDEX(' ', strSingleKeyword) + 1)))) AS field1
                       FROM         (SELECT     LTRIM(RTRIM(strSingleKeyword)) AS strSingleKeyword
                                              FROM          tableKeywords AS der_table1) AS derivedtbl_1_1
                       WHERE     (CHARINDEX(' ', strSingleKeyword, CHARINDEX(' ', strSingleKeyword) + 1) > LEN(strSingleKeyword))) AS derived_table4
GROUP BY field1
ORDER BY field1

Open in new window

0
 
LVL 3

Expert Comment

by:FreakyEddie
ID: 33648207
Perhaps these articles will help you:
SQLTips
WeAsk
 
It explains and shows examples of the CharIndex function in SQL
 
Good luck!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33658830
Since you are using MS SQL Server you may want to consider using Full-Text Search.
0
 
LVL 6

Author Comment

by:matija_
ID: 33661245
I'm using MS Access database :\
0
 
LVL 3

Expert Comment

by:FreakyEddie
ID: 33661267
The query didn't work out for you?
0
 
LVL 6

Author Comment

by:matija_
ID: 33661358
@FreakyEddie
I have rewritten MSSQL string functions to Access equivalent, but the query is inscrutable to me. What is "strSingleKeyword"? My table has only 1 column named "keywords" which should match "field1" from your query.
0
 
LVL 3

Expert Comment

by:FreakyEddie
ID: 33661378
strSingleKeyword is the name of the field.
tableKeywords is the name of the table.

So if you change strSingleKey to the fieldname of your own Database and change tableKeyword to the name of your table it should work.
0
 
LVL 6

Author Comment

by:matija_
ID: 33662005
I have converted it to Access syntax, but it's not counting right - can you take a look to my query please:

SQL = "SELECT Keyword, COUNT(*) FROM (SELECT MID(strSingleKey, 1, INSTR(strSingleKey,' ')) AS Keyword FROM tableKeywords "
SQL = SQL & "UNION ALL SELECT LTRIM(RTRIM(MID(strSingleKey, INSTR(strSingleKey,' ') + 1, INSTR(INSTR(strSingleKey,' ') + 1, strSingleKey, ' ') - INSTR(strSingleKey,' ')) )) AS Keyword "
SQL = SQL & "FROM (SELECT LTRIM(RTRIM(strSingleKey)) AS strSingleKey FROM tableKeywords) WHERE (strSingleKey LIKE '%  %')) "
SQL = SQL & "GROUP BY Keyword ORDER BY COUNT(*) DESC"
0
 
LVL 3

Expert Comment

by:FreakyEddie
ID: 33662239
There's a double space between %  % in WHERE (strSingleKey LIKE '%  %')) "
0
 
LVL 3

Expert Comment

by:FreakyEddie
ID: 33662271
The Where-clause is only there because it will return an error when you substring a string which doesn't have a spece. So you should also include the strings in it which don't have spaces.

Use another substring for that like
Select Keyword FROM tableKeywords WHERE NOT(strSingleKey LIKE '% %')
UNION ALL
other substrings

or something like that
0
 
LVL 6

Author Comment

by:matija_
ID: 33662359
I've left double space in "LIKE '%  %'" because it return error otherwise.
I think the problem lies within this line:

SQL = SQL & "UNION ALL SELECT LTRIM(RTRIM(MID(SearchTerm, INSTR(SearchTerm,' ')+1, INSTR(SearchTerm, ' ', INSTR(SearchTerm, ' ')+1) - INSTR(SearchTerm, ' ') ))) AS Keyword "

I can get the results, but it's counting wrong. Eg. I have the follwing rows:
"new york"
"new"
"york"
"york new"
"new york new"

and it returns:
2x "new"
2x ""
1x "york"

I'm a bit lost in this query :(
0
 
LVL 3

Expert Comment

by:FreakyEddie
ID: 33662429
check this site.
this ones a bit better explained than mine, but it uses - in stead of spaces:

http://stackoverflow.com/questions/630907
But i think you'll manage.
0
 
LVL 6

Author Comment

by:matija_
ID: 33664458
Thanks for all the inspiration @FreakyEddie, I've managed to rewrite your code snippet (using small hacks) to work flawlessly with MS Access database.

Final code:
SQL = "SELECT Keyword, COUNT(Keyword) FROM ("
SQL = SQL & "SELECT strColumnName AS Keyword FROM strTable WHERE NOT strColumnName LIKE '% %' "
SQL = SQL & "UNION ALL "
SQL = SQL & "SELECT MID(strColumnName, 1, INSTR(strColumnName, ' ')) AS Keyword FROM strTable WHERE strColumnName LIKE '% %' "
SQL = SQL & "UNION ALL "
SQL = SQL & "SELECT MID(strColumnName, INSTR(strColumnName, ' ') + 1, INSTR(INSTR(strColumnName + ' ', ' ') + 1, strColumnName + ' ', ' ') - INSTR(strColumnName + ' ', ' ')) AS Keyword FROM "
SQL = SQL & "(SELECT strColumnName AS strColumnName FROM strTable WHERE strColumnName LIKE '% %') "
SQL = SQL & ") GROUP BY Keyword ORDER BY COUNT(Keyword) DESC"

It's parsing only first 2 words for now, but with easy modification, more words can be added. Hope it helps someone in the future.
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33664484
you should really award the points to freakyeddie :-)
0
 
LVL 6

Author Comment

by:matija_
ID: 33664485
Why 0 points to FreakyEddie's comment #33647969 when I selected 500pts x A multiplier?
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33664499
perhaps something went wrong?
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33664517
at least your intentions are clear, as soon as a zone advisor shows up they can close it correctly
0
 
LVL 6

Author Comment

by:matija_
ID: 33664524
I have chosen FreakyEddie's answer as Best Solution which solved the question, entered 500 pts and chose A grade multiplier, but it says 0 points awarded when I close it... wtf?
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33664564
dont worry you supplied all the information they need to fix it. it will just take a while longer now.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33668066
>>I'm using MS Access database :\<<
Which of course begs the question as to why you would accept a solution that is unusable as is in MS Access.
0
 
LVL 6

Author Comment

by:matija_
ID: 33670419
@acperkins
It only begs the question if you hadn't read my final comment #33664458. I have awarded the FreakyEddie's comment as it has inspired me to rewrite his MSSQL solution into MS Access, which I have shared with the rest of you. I wanted to accept my own comment #33664458 as assisted solution (with no points given of course), but the EE bugged at that point giving no points to FreakyEddie.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question