From the example table above, I want to return one row per route with their most common type/form of Incident. Such that it would look like this:

I have tried the following query, but I am unsure whether or not it returns the most common form of Incident per route:

SELECT Route
   , Incident
FROM bus_delay;

I have also attempted to use COUNT(DISTINCT) but I require the Incident type returned as string, per route:

SELECT DISTINCT Route
    , Incident
    , COUNT(Incident) AS count_incident
FROM bus_delay
GROUP BY Incident
    , Route;

How do I query such a table to return one row per Route, with that row only showing the most common form of Incident for that Route?

CodePudding user response：

SELECT Z.Route,Z.Incident  FROM
(
  SELECT C.Route,C.Incident,
   ROW_NUMBER()OVER(PARTITION BY C.Route,C.Incident ORDER BY (SELECT NULL))XCOL
  FROM YOUR_TABLE AS C
)Z WHERE Z.XCOL>1

CodePudding user response：

Use WINDOW function and CTE

WITH CTE as(
   SELECT
      Route,    Incident,   delay_minute,
      ROW_NUMBER() OVER(PARTITION BY Route,     Inciden ORDER BY delay_minute DESC) rn
   FROM bus_delay)
SELECT
Route,  Incident,   delay_minute
FROM CTE WHERE rn = 1

CodePudding user response：

Try the following:

select Route, Incident
from
(
  select Route, Incident, 
       row_number() over (partition by Route order by count(*) desc) rn
  from bus_delay
  group by Route, Incident
) T
where rn=1

If there could be a ties in the Incident counts per route (multiple counts with the same value) and you want to return all of Incident with the maximum count then use DENSE_RANK instead of ROW_NUMBER.

See a demo.