How to use count for each row PostgreSQL

Question

I have a table in pgadmin for postgres which holds two private keys from two other tables. Its about modeling a hospital database in PostgreSQL. I created two tables "bed" and "room". I save the private keys from this tables in a table called "bed_rooms" which will give information about how many beds are inside a room. So far the theory. I added a picture of my table "betten_zimmer" which is german and stands for "bed_rooms".

enter image description here I want to make a query which tells me how many beds a room is holding. I want to see every row with the number of beds. My next idea is to add a trigger which will fire when a room holds more than 4 beds or less than 0.

How do I make this query? AND how would the trigger look?

This is my code so far. I have 60 rooms and cant add 60 times OR .. = 5 .. 60

SELECT zimmerid, count(*)
FROM betten_zimmer
WHERE zimmerid = 1 OR zimmerid = 2 OR zimmerid = 3 OR zimmerid = 4
GROUP BY zimmerid

Thanks in advance. If anything is unclear comment this post.

aleroot · Accepted Answer · 2015-06-25 20:36:44Z

3

If I well understood you only need a BETWEEN construct :

WHERE zimmerid BETWEEN 1 AND 60

edited Jun 25, 2015 at 20:36

answered Jun 25, 2015 at 20:35

aleroot

72.9k32 gold badges182 silver badges220 bronze badges

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

2 Comments

Matthis Kohli Over a year ago

SELECT zimmerid, count(*) FROM betten_zimmer WHERE zimmerid BETWEEN 1 AND 60 GROUP BY zimmerid ORDER BY zimmerid Thanks just was i looking for.

aleroot Over a year ago

Don't ask multiple questions through comments, please open up another question ...

Collectives™ on Stack Overflow

How to use count for each row PostgreSQL

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related