Geek asks and answers

How to 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 a lot of other data. This could be normalized if necessary, initially I did not think what I needed, but maybe I needed. Anyway,

I need a query that takes into account only the screenWidth values ​​once for each user, so the result will look like this:

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

Instead of counting 1366 as 4, this will avoid over-enumerating the data.

Is there a way to write a query for this?

+2
source share
2 answers

You should get the number of DISTINCT users for each screen width, and here is an example query that will retrieve the results.

Click here to view 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;

Conclusion:

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

: COUNT DISTINCT:

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

More articles:


All Articles