Subquery count() for postgres query

Question

I have two tables with web traffic that I am joining and visualizing on a map.

I am trying to write a counter that creates a query result with a count of the number of times a particular IP address shows up in the logs. I think this will take the form of a subquery that returns the count of rows of the specific row the main query is selecting.

When I run the following query, the error I get is more than one row returned by a subquery used as an expression.

select
squarespace_ip_addresses.ip,
squarespace_ip_addresses.latitude,
squarespace_ip_addresses.longitude,
st_SetSrid(ST_MAKEPOINT(squarespace_ip_addresses.longitude, squarespace_ip_addresses.latitude), 4326) as geom,
(select count(hostname) from squarespace_logs group by hostname) as counter,
squarespace_logs.referrer
from
squarespace_ip_addresses
left outer join
squarespace_logs
on
squarespace_ip_addresses.ip = squarespace_logs.hostname

Something that has been suggested to me is a subquery in a select that filters by the main query output runs that query for every row.

Does anyone have any insights?

klin · Accepted Answer · 2018-12-07 03:23:08Z

2

Aggregate the data in a derived table (a subquery in FROM clause):

select
    a.ip,
    a.latitude,
    a.longitude,
    st_SetSrid(ST_MAKEPOINT(a.longitude, a.latitude), 4326) as geom,    
    c.count,
    l.referrer
from squarespace_ip_addresses a
left join squarespace_logs l on a.ip = l.hostname
left join (
    select hostname, count(*)
    from squarespace_logs
    group by hostname
    ) c on a.ip = c.hostname

answered Dec 7, 2018 at 3:23

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

reallymemorable Over a year ago

So basically this is joining three tables together and gets around the multiple returned columns problem by just accepting that you will get two columns back and joining them. Is that right?

reallymemorable Over a year ago

Also why is hostname necessary in select hostname, count(*) in the subquery? I don't understand why it can't just be select count(*).

klin Over a year ago

Basically yes. hostname in the subquery is for joining with the main table.

reallymemorable Over a year ago

How would I extend this to query only rows that have a count greater than a given number?

klin Over a year ago

Add where count > 10 at the end.

Collectives™ on Stack Overflow

Subquery count() for postgres query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related