PHP & MySQL: Count number of similar rows with one row same, in one statement

Question

I have a table with columns: userid, productid

I want to do the following in one statement: For each member count the number of productids that match the productids for another specified member.

Example table:

userid productid
1      10
1      14
2      10
3      12
3      14
3      10
3      19
4      15

In the above example, if I specified userid 1 then I want to get a result that looks like:

userid matches
2      1
3      2
4      0

The result would look like the above because these are the productids from each user that match the productids from my specified user (1).

In reality I would also want to order it by matches desc and only where matches>0. So it would be:

userid matches
3      2
2      1

Any ideas?

@JohnConde, well... SQL statements either work or they don't work. So far I have been unable to make it do what I want. I don't think it would be helpful to list here many incorrect SQL statements. — Alasdair
– Alasdair, Commented Apr 9, 2013 at 15:23
If they don't work we can tell you what you;re doing wrong. If you don't show us anything, we're just doing your work for you. — John Conde
– John Conde, Commented Apr 9, 2013 at 15:25

John Woo · Accepted Answer · 2013-04-09 15:23:59Z

1

SELECT  a.UserID, COUNT(*) `matches`
FROM    TableName a
        INNER JOIN 
        (
            SELECT  ProductID
            FROM    TableName
            WHERE   userID = 1
        ) b ON a.ProductID = b.ProductID
WHERE   a.userID <> 1
GROUP   BY a.UserID
HAVING  COUNT(*) > 0

SQLFiddle Demo

THE OUTPUT

╔════════╦═════════╗
║ USERID ║ MATCHES ║
╠════════╬═════════╣
║      2 ║       1 ║
║      3 ║       2 ║
╚════════╩═════════╝

answered Apr 9, 2013 at 15:23

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP & MySQL: Count number of similar rows with one row same, in one statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related