I have a some data in the following format:

I want to count the Routes and then show only the most popular 2, grouped by Bus :

I have the following so far:

SELECT Bus, Route, COUNT(Route) 
FROM my_table
GROUP BY Bus, Route
ORDER BY Bus, COUNT DESC

I have looked at Rank / Partition /Limit but I can't get the COUNT field to work, nor can I work out the correct syntax.

CodePudding user response：

Solution a bit twisted :

  WITH list AS
    (
    SELECT Bus, Route, COUNT(Route) AS count
    FROM test
    GROUP BY Bus, Route
    ), consolidated_list AS
    (
    SELECT jsonb_agg(row_to_json(l.*) ORDER BY l.count DESC) AS list
      FROM list AS l
     GROUP BY Bus
    )
    SELECT j->>'bus' AS bus
         , j->>'route' AS Route
         , j->>'count' AS count
      FROM consolidated_list AS l
     CROSS JOIN LATERAL jsonb_path_query (l.list :: jsonb, '$[0,1]') AS j

Result :

bus         route   count
Slowcoach   SC555   4
Slowcoach   SC123   3
SpeedyTram  ST222   4
SpeedyTram  ST111   2

see details in dbfiddle

CodePudding user response：

Group and count (ti) then order and rank (tx) and select these with rank <= 2.

select bus, route, cnt
from
(
  select *, row_number() over (partition by bus order by cnt desc) r
  from 
  (
    select bus, route, count(*) cnt 
    from the_table
    group by bus, route
  ) ti 
) tx
where r <= 2;

SQL fiddle