不同列A组b的个数

Question

我有一个包含以下数据的表。

SELECT *
FROM temp_table LIMIT 5
id  trip_id segment_id  session_id  start_timestamp     lat_start   lon_start   lat_end     lon_end travelmode
563097015   563097  15  128618  2017-05-20 17:47:12+01  41.1783308  -8.5949878  41.1784478  -8.5948463  0
563097013   563097  13  128618  2017-05-20 17:45:29+01  41.1781344  -8.5951169  41.1782919  -8.5950689  0
563097011   563097  11  128618  2017-05-20 17:43:41+01  41.1781196  -8.5954075  41.1782139  -8.5950689  0
563097009   563097  9   128618  2017-05-20 17:41:48+01  41.1782497  -8.595197   41.1781101  -8.5954124  0
563097003   563097  3   128618  2017-05-20 17:10:29+01  41.1832512  -8.6081606  41.1782561  -8.5950259  0

我知道travelmode有5个不同的值。因此，我想通过不同的travelmode来计数片段(segment_id)的数量。

表示0和travelmode的段数;有多少1是travelmode等

编辑

所需输出:

travelmode  segments
0   273
1   143
2   198
3   236
4   120

Answer 1

请尝试在count函数中使用DISTINCT关键字:

SELECT travelmode, COUNT(DISTINCT segment_id) NumOfSegments
FROM temp_table
GROUP BY travelmode

Answer 2

试试这个:

Select Distinct travelmode, Count(*) as Count 
from tempTable 
Group By travelMode