This is my query:

WITH subtable AS (
SELECT
    member_casual,
    seasons,
    start_station_name || ' to ' || end_station_name AS route
FROM
    bike_data
)

SELECT
    member_casual,
    seasons,
    MODE() WITHIN GROUP (ORDER BY route) AS most_frequent_route, 
    COUNT(*) AS total_service_used_count
FROM
    subtable
GROUP BY
    member_casual,
    seasons;

And this is the result:

The difficulty comes in when I want to include the frequency/count of the most_frequent_route as a new column in this groupby result. Before asking here, I asked a nice senior person before and he said:

Put the main query into a CTE, then in the new outer query you can write a scalar subquery that performs the count over the subtable where the subtable rows match those from the grouping CTE.

Honestly I don't really understand the core logic of this advice, so I can't really put all of these together as a complete query. Can anyone give me some example how to make it work? Thank you in advance!

CodePudding user response：

Maybe the nice senior person meant this:

WITH subtable_a AS (
SELECT
    member_casual,
    seasons,
    start_station_name || ' to ' || end_station_name AS route
FROM
    bike_data
),
subtable_b as
(
SELECT
    member_casual,
    seasons,
    MODE() WITHIN GROUP (ORDER BY route) AS most_frequent_route, 
    COUNT(*) AS total_service_used_count
FROM
    subtable_a
GROUP BY
    member_casual,
    seasons
)
select sb.*, 
(
  SELECT count(*) 
  from subtable_a sa 
  where sa.member_casual = sb.member_casual
  and sa.seasons = sb.seasons
  and sa.route = sb.most_frequent_route
) as most_frequent_route_count
from subtable_b sb;

Your subtable becomes subtable_a, your main query becomes subtable_b (Put the main query into a CTE) and in the new main (outer) query select all from subtable_b and a scalar subquery column - the inner capitalized SELECT count(*) in parentheses - that extracts most_frequent_route_count.