Count records with same id but different column value

Question

I have the following table:

+------+------+
|userID|Name  |
+------+------+
|1     |sport |
+------+------+
|2     |it    |
+------+------+
|3     |game  |
+------+------+
|1     |sport |
+------+------+
|1     |game  |
+------+------+
|3     |it    |
+------+------+
|3     |sport |
+------+------+
|2     |it    |
+------+------+

I want to count the total number of records per UserID and if all of those are unique/have the same Name. Example:

• userID 1: sport, sport, game
• userID 2: it, it
• userID 3: game, it, sport

I should get:

• userId 1: 3, False
• userID 2: 2, True
• userID 3: 3, False

Answer 1

The total number of rows per UserID is easy, you just need to use COUNT(*). As for the other column, then, assuming Name cannot be null, you need to count distinct Name values and compare the result to 1. To explain: if all names are identical, COUNT(DISTINCT Name)) will return 1, otherwise it will return a different number. Thus, by comparing the result to 1 you will determine whether all names are unique or not.

This is how I would implement it in SQL:

SELECT
  UserID,
  COUNT(*)                   AS TotalRows,
  (COUNT(DISTINCT Name) = 1) AS AllNamesUnique
FROM
  YourTable
GROUP BY
  UserID
;

Feel free to play with this solution at dbfiddle.uk.