SQL, как я могу использовать отчетливую функцию count в соединении

Question

Я новичок в SQL и использую Postgres 9.6, у меня есть 2 запроса, и я хотел бы объединить их в 1 . Я хочу использовать функцию Count и порядок по полю в порядке убывания, что я сделал здесь

1-й запрос

select count(s.city)::text as most_topics,
city::text || ', ' || state::text AS location from streams s 
group by (location) order by most_topics desc limit 5

Этот первый запрос - это именно та информация, которую я хочу получить в том же порядке, проблема в том, что мне нужно выполнить внутреннее соединение и получить данные из второй таблицы, которую я сделал здесь. Этот второй запрос получает все данные, которые мне нужны, однако я не знаю, как использовать функцию Count() во втором запросе, любые предложения будут хорошими. Чтобы подвести итог, первый запрос дает мне половину данных, которые я хочу, так как мне нужно объединить вторые данные, называемые почтовыми индексами в полях (город и штат) . Второй запрос дает мне все данные, которые мне нужны, но у меня не получается, что там работает функция Count() .

2-й запрос

select DISTINCT ON(s.city,s.state)s.city as location,
z.latitudes,z.statelong,z.longitudes,z.thirtylatmin,z.thirtylatmax
 ,z.longitudes,z.thirtylatmin,z.thirtylatmax,     
 z.thirtylonmin,z.thirtylonmax from streams s inner join zips z
 on (s.city=z.city and s.state=z.state) where  order by s.city,s.state desc limit 5

результаты первого запроса

результаты второго запроса: Лутц должен быть сверху, а не снизу

Answer 1

Вы можете использовать CTE или подзапросы:

with l5 as (
      select count(s.city)::text as most_topics,
             city::text || ', ' || state::text AS location,
             city, state
      from streams s 
      group by city, state
      order by most_topics desc
      limit 5
     ) 
select distinct on (l5.locatin) l5.location, l5.most_topics, z.*
from l5 join
     zips z
     on l5.city = z.city and l5.state = z.state
order by l5.location;