1

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

1 Answer 1

5

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.

1
  • Very neat Andriy! +1
    – Vérace
    May 21, 2018 at 6:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.