Number of distinct column A group by B-CodePudding

I have a table containing the following data.

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

And I know that I have 5 distinct value of travelmode. So I want to count number of segments (segment_id) by distinct travelmode.

That's how many segments are 0 as travelmode; how many have 1 as travelmode etc

EDIT

Required output:

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

CodePudding user response：

Please try to use DISTINCT keyword inside your count function:

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

CodePudding user response：

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

Try this.