CODEWITHSUNDEEP
sqlpostgresql
eje211
eje211

Reputation: 2429

In PostgreSQL, get a minimum value if there is none

Sorry if the title did not match the actual question. I'm not sure how to phrase this.

In Postgres, I have users that can be at a station. The stations are numbered 1 to 5. I have a table users and it has a column station. If a user is at a station, station value is the number of the station, if not, it is NULL. Here's how I find the number of users at each station:

SELECT station, count(users) AS users
    FROM users
    WHERE station IS NOT NULL
    GROUP BY station ORDER BY station;

This yields:

 station | users 
---------+---------
       1 |     4
       2 |     1
       3 |     1

But that's not really true. There are zero users on station 4 and 5. How can I address that? I would like to use this query as a view to calculate other things, but I need all the stations there. I don't need a table for the stations, as far as I know: I'm not going to use them anywhere else.

So how do I account for the unused stations? Do I really need to make a table for them and reference the stations table from the users table or is there a simpler way?

Upvotes: 0

Views: 162

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Assuming that you have a table stations, you can handle this with a left join:

SELECT s.station, count(u.station) AS users
FROM stations s LEFT JOIN
     users u
     ON s.station = u.station
GROUP BY s.station
ORDER BY s.station;

Without such a table, you can use generate_series():

SELECT s.station, count(u.station) AS users
FROM generate_series(1, 5) as s(station) LEFT JOIN
     users u
     ON s.station = u.station
GROUP BY s.station
ORDER BY s.station;

Upvotes: 4

Related Questions