2

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?

Rich Bradshaw
  • 71,795
  • 44
  • 182
  • 241

2 Answers2

6

short and simple: use COUNT DISTINCT:

SELECT
  screenWidth,
  COUNT(DISTINCT user_id)
FROM
  mytable
GROUP BY
  screenWidth;
oezi
  • 51,017
  • 10
  • 98
  • 115
  • I just had to add 'GROUP BY screenWidth' to the end, but then it works. Thanks a lot! I was putting DISTINCT after the select, wasn't aware you could use it like this. Thanks! – Rich Bradshaw Apr 29 '12 at 15:07
5

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