CODEWITHSUNDEEP
mysql
Mawg
Mawg

Reputation: 40169

Counting and ordering

mysql> describe taps;
+------------+-----------+------+-----+-------------------+-------+
| Field      | Type      | Null | Key | Default           | Extra |
+------------+-----------+------+-----+-------------------+-------+
| tag        | int(11)   | NO   |     | NULL              |       |
| station    | int(11)   | NO   |     | NULL              |       |
| time_Stamp | timestamp | NO   |     | CURRENT_TIMESTAMP |       |
+------------+-----------+------+-----+-------------------+-------+
3 rows in set (0.00 sec)

How do I get the number of staions visited by each tag and sort by the most visits - where we only count one visit to each staion, even if there were several?

Random data: 

mysql> select * from taps;
+-----+---------+---------------------+
| tag | station | time_Stamp          |
+-----+---------+---------------------+
|  50 |      12 | 2016-05-10 20:02:41 |
|  33 |       7 | 2016-05-10 20:02:42 |
|  34 |      13 | 2016-05-10 20:02:42 |
|  31 |       3 | 2016-05-10 20:02:42 |
|   9 |      10 | 2016-05-10 20:02:42 |
|   2 |      11 | 2016-05-10 20:02:42 |
|  33 |      13 | 2016-05-10 20:02:42 |
|  46 |      12 | 2016-05-10 20:02:42 |
|  45 |       1 | 2016-05-10 20:02:42 |
|  34 |       7 | 2016-05-10 20:02:42 |
|   4 |      10 | 2016-05-10 20:08:18 |
|  17 |       5 | 2016-05-10 20:08:19 |
|  46 |       7 | 2016-05-10 20:08:19 |
|  16 |       5 | 2016-05-10 20:08:19 |
|  33 |       3 | 2016-05-10 20:08:19 |
|  31 |       4 | 2016-05-10 20:08:19 |
|   1 |      13 | 2016-05-10 20:08:19 |
|  35 |       2 | 2016-05-10 20:08:19 |
|   9 |      12 | 2016-05-10 20:08:19 |
|  26 |       7 | 2016-05-10 20:08:19 |
+-----+---------+---------------------+
20 rows in set (0.00 sec)

Upvotes: 0

Views: 30

Answers (2)

Ullas
Ullas

Reputation: 11556

Use COUNT funtion and GROUP BY to find the number of visits for each tag.

And use ORDER BY clause to sort the result in descending of the number of visits.

Query

SELECT tag, COUNT(DISTINCT station) AS `no of visits`
FROM taps
GROUP BY tag
ORDER BY COUNT(DISTINCT station) DESC;

Upvotes: 1

sgeddes
sgeddes

Reputation: 62831

Assuming I'm understanding your question correctly, you just need to use count with distinct:

select tag, count(distinct station) cnt
from taps
group by tag
order by 2 desc

Upvotes: 1

Related Questions