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.

score 1 · Answer 1 · answered Dec 11 '14 at 11:44

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 '14 at 11:44

Gergely Zsamboki

313
2
7

Yep, that worked for me! But according to https://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 :( – Opux Sep 12 '18 at 14:26

score 0 · Accepted Answer · edited Mar 03 '14 at 12:10

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 03 '14 at 12:10

Clodoaldo Neto

118,695
26
233
260

answered Mar 03 '14 at 06:12

amudhan3093

740
9
17

I think you mean `left join` – Clodoaldo Neto Mar 03 '14 at 12:12

score 0 · Answer 3 · answered Mar 03 '14 at 06:16

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 03 '14 at 06:16

Farvardin

5,336
5
33
54

Although you are not writing the `join` world you are still doing a `join` – Clodoaldo Neto Mar 03 '14 at 12:07

select subquery in postgresql get list

3 Answers3