Maintain the current count of buildings in a different table

Question

I have two tables:

Table apartments:

house_no	house_street
1	Pomona
2	Pomona
1	Dubai
2	Dubai

Table streets:

street_name	total_buildings
Dubai	NULL
Pomona	NULL

I would love the column streets.total_buildings to be occupied by the COUNT of the total house_no values under each unique house_street (Dubai, Pomona,...) in table apartments.

streets.street_name should represent the house_street column in table apartments and streets.total_buidlings should be updated automatically upon every entry in table apartments.

I tried the code to extract the count of the number of house_no values under a house_street in table apartments:

SELECT count(house_street), house_street
FROM apartments
GROUP BY house_street

I got the result:

count	house_street
2	Pomona
2	Dubai

But this does not fully solve the problem?

Answer 1

Replace the table streets with a view:

CREATE VIEW streets AS
SELECT house_street AS street_name, count(house_no) AS total_buildings
FROM   apartments
GROUP  BY 1;

It's in the nature of a view that it's always up to date.

If house_no is defined NOT NULL (or maybe in any case if you want to count those null values, too) you can replace count(house_no) with count(*).