SQL how can I use distinct on with the count function inside a join

Question

I am new to sql and am using postgres 9.6, I have 2 queries and I would like to join them into 1 . I want to use the Count function and order by a field in descending order which I have done here

1st query

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

That first query is exactly the information I want in the same exact order, the issue is that I need to do an inner join and get data from a second table which I have done here . This second query gets all the data I need however I do not know how to use the Count() function in the second query any suggestions would be great . To sum things up the 1st query gives me halve the data I want as I need to join on a second data called zips on the fields (city and state) . The second query gives me all the data I need but I can't seem to have the Count() function working there .

2nd query

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

first query results

second query results: Lutz is supposed to be on top not bottom

Gordon Linoff · Accepted Answer · 2017-06-07T18:21:20.370

You can use CTEs or subqueries:

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;

SQL how can I use distinct on with the count function inside a join

1 Answers1