select subquery in postgresql get list

Question

I have Table:

stations (staion , phone , address)

AND

members (username , password , fullname , station)

I want to Station List , count username . So I wrote this query.

SELECT 
    stations.station as st,
    (
        SELECT COUNT(username) 
        FROM members 
        WHERE members.station = stations.station
    ) as co
FROM stations
GROUP BY stations.station
ORDER BY stations.station

It alerts (Error) :

'ERROR: subquery uses ungrouped column "stations.station" from outer query'

Please help me to get right data with PostgreSQL.

Gergely Zsamboki · Accepted Answer · 2014-12-11 11:44:59Z

1

The proper solution is this:

SELECT 
stations.station as st,
(
    SELECT COUNT(username) 
    FROM members 
    WHERE members.station = ANY( array_agg( stations.station ) )
) as co
FROM stations
GROUP BY stations.station
ORDER BY stations.station

answered Dec 11, 2014 at 11:44

Gergely Zsamboki

3132 silver badges7 bronze badges

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

1 Comment

Opux Over a year ago

Yep, that worked for me! But according to stackoverflow.com/a/34627688/1676382 =ANY is less likely to use an index. However, when I tried a solution using IN array_to_string(array_agg(stations.station), ',') as a template, it still didn't didn't use an index for me :(

Clodoaldo Neto · Accepted Answer · 2014-03-03 12:10:13Z

0

Try this

select stations.station as st,count(username) as co 
from member left join stations on member.station=stations.station 
group by stations.station 
order by stations.station;

edited Mar 3, 2014 at 12:10

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

answered Mar 3, 2014 at 6:12

amudhan3093

7481 gold badge9 silver badges17 bronze badges

1 Comment

Clodoaldo Neto Over a year ago

I think you mean left join

Farvardin · Accepted Answer · 2014-03-03 06:16:19Z

0

simple answer without join is:

SELECT  stations.station, COUNT(username)
FROM stations, members
WHERE members.station = stations.station
GROUP BY stations.station
ORDER BY stations.station

answered Mar 3, 2014 at 6:16

Farvardin

5,4226 gold badges36 silver badges54 bronze badges

1 Comment

Clodoaldo Neto Over a year ago

Although you are not writing the join world you are still doing a join

Collectives™ on Stack Overflow

select subquery in postgresql get list

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related