CODEWITHSUNDEEP
phpmysql
moonwalker
moonwalker

Reputation: 485

Comparing fields and getting percentage

Hi and thank you again for looking into this question.

I have one mysql table for visitors. They can add their favorite singers to a list. So the table looks very simple:

user_artist_favorites
id
user_id
artistName

Let's say Moonwalker got 4 favorite singers:

 1. Michael Jackson
 2. Elvis Presley
 3. Ray Charles
 4. Stevie Wonder

The user Tester got also 4 favorite singers:

 1. Michael Jackson
 2. Ray Charles
 3. Stevie Wonder
 4. Lady Gaga (sorry folks)

What I would like to do now is compare the two users and output something like: Moonwalker & Tester have 75% the same taste in music.

How to start? Can anyone point me to the right direction please?

Thanks in advance for any help you can provide.

Upvotes: 1

Views: 202

Answers (2)

Thomas
Thomas

Reputation: 64645

Select T1.user_id
    , Count( T2.user_id ) / Count( T1.user_id ) * 100.0 As PercentMatch
From user_artist_favorites As T1
    Left Join user_artist_favorites As T2
        On T2.artistname = T1.artistname
            And T2.user_id = 'Tester'
Where T1.user_id = 'Moonwalker'
Group By T1.user_id
Union All
Select T1.user_id
    , Count( T2.user_id ) / Count( T1.user_id ) * 100.0
From user_artist_favorites As T1
    Left Join user_artist_favorites As T2
        On T2.artistname = T1.artistname
            And T2.user_id = 'Moonwalker'
Where T1.user_id = 'Tester'
Group By T1.user_id

The first part of the Union All query returns the percent that Tester is like Moonwalker. The second query returns the percent that Moonwalker is like Tester.

Upvotes: 4

Rasika
Rasika

Reputation: 1998

What you can do is read the lists sorted by artist name for the two users and go through a loop to count the number of singers that match. The loop will be a bit complicated as you need to check which list is alphabetically greater at the index and increase the pointer on the one that is lower.

Upvotes: 1

Related Questions