select users have more than one distinct records in mysql

Question

For a table that holds the records of user's webpages visiting behavior, how can I select users that visit more than one webpages.

The structure of this tables is:

userId        webpageId       visitTime
  0              123            ...
  0              124            ...
  1              123            ...
 ...             ...            ...

I can count using:

SELECT userId, COUNT(DISTINCT webpageId) AS count FROM visits GROUP BY userId;

It gives me the result like:

userId          count
  0               2
  1               1
  2               6
 ...             ...

How can I excute query that gives me the final result like:

userId
  0
  2
 ...

each is user that visit more than one DISTINCT webpages

Answer 1

just add having clause

SELECT userId, COUNT(DISTINCT webpageId) AS count 
FROM visits 
GROUP BY userId
HAVING COUNT(DISTINCT webpageId) > 1

but if you only what the ID

SELECT userId
FROM visits 
GROUP BY userId
HAVING COUNT(DISTINCT webpageId) > 1

• SQLFiddle Demo

the reason why you are filtering on HAVING clause and not on WHERE is because, WHERE clause cannot support columns that where aggregated.