CODEWITHSUNDEEP
mysqlsql
Rich Bradshaw
Rich Bradshaw

Reputation: 72985

How do I count only the first occurrence of a value?

I have a table like this;

+----+---------+-------------+
| id | user_id | screenWidth |
+----+---------+-------------+
|  1 |       1 |        1366 |
|  2 |       1 |        1366 |
|  3 |       1 |        1366 |
|  4 |       1 |        1366 |
|  5 |       2 |        1920 |
|  6 |       2 |        1920 |
|  7 |       3 |        1920 |
|  8 |       4 |        1280 |
|  9 |       5 |        1280 |
| 10 |       6 |        1280 |
+----+---------+-------------+

Along with loads of other data. This could be normalised if needed, originally I didn't think I'd need to, but perhaps I should. Anyway,

I'd like a query that only counts the screenWidth values once per user, so the output would look like:

+-------------+-------+
| screenWidth | count |
+-------------+-------+
|        1366 |     1 |
|        1920 |     2 |
|        1280 |     3 |
+-------------+-------+

Rather than counting 1366 as 4 - this would avoid heavy users from skewing the data.

Is there a way to write a query to do this?

Upvotes: 2

Views: 9236

Answers (2)

oezi
oezi

Reputation: 51817

short and simple: use COUNT DISTINCT:

SELECT
  screenWidth,
  COUNT(DISTINCT user_id)
FROM
  mytable
GROUP BY
  screenWidth;

Upvotes: 6

user756519
user756519

Reputation:

You have to get the DISTINCT count of users per screenwidth and here is the sample query that will fetch the results.

Click here to view the demo in SQL Fiddle

Script:

CREATE TABLE screenwidth 
(
    id INT NOT NULL
  , user_id INT NOT NULL
  , screenwidth INT NOT NULL
);

INSERT INTO screenwidth (id, user_id, screenwidth) VALUES
  (1, 1, 1366),
  (2, 1, 1366),
  (3, 1, 1366),
  (4, 1, 1366),
  (5, 2, 1920),
  (6, 2, 1920),
  (7, 3, 1920),
  (8, 4, 1280),
  (9, 5, 1280),
  (10, 6, 1280);

SELECT      screenwidth
        ,   COUNT(DISTINCT user_id) AS screenwidthcount
FROM        screenwidth
GROUP BY    screenwidth
ORDER BY    screenwidthcount;

Output:

SCREENWIDTH SCREENWIDTHCOUNT
----------- ----------------
1366               1
1920               2
1280               3

Upvotes: 5

Related Questions