PostgreSQL 9.5 Multiple SELECT in SELECT

Question

For the information, the general purpose of this question is to be able to work in QGIS with an updatable view system.

Let's say I have a table "building" and a table "apartment". Let's say the tables are defined as such :

CREATE TABLE building (
    id_building INTEGER PRIMARY KEY, 
    adress VARCHAR(255)
    );

CREATE TABLE apartment (
    id_apartment INTEGER PRIMARY KEY,
    floor INTEGER,
    id_building INTEGER,
    CONSTRAINT fk1 FOREIGN KEY (id_building) REFERENCES building (id_building)
    );

INSERT INTO building VALUES (1, 'adress1');
INSERT INTO building VALUES (2, 'adress2');

INSERT INTO apartment VALUES (1, 0, 1);
INSERT INTO apartment VALUES (2, 0, 1);
INSERT INTO apartment VALUES (3, 0, 1);
INSERT INTO apartment VALUES (4, 1, 1);
INSERT INTO apartment VALUES (5, 1, 1);
INSERT INTO apartment VALUES (6, 1, 1);
INSERT INTO apartment VALUES (7, 2, 1);
INSERT INTO apartment VALUES (8, 2, 1);
INSERT INTO apartment VALUES (9, 0, 2);
INSERT INTO apartment VALUES (10, 1, 2);
INSERT INTO apartment VALUES (11, 1, 2);
INSERT INTO apartment VALUES (12, 2, 2);
INSERT INTO apartment VALUES (13, 2, 2);

I would like, in a view of the table building, to display the number of apartment grouped by floor. If it is possible, what would be the way to do so ? And since the number of floors can vary, it would be even better to be able to automatically generate a somehow flexible view relatively to the number of floors of each building.

There is a version of this code here: http://rextester.com/MTIJ52125

Thanks for your time.

Ben H · Accepted Answer · 2017-07-27 09:04:07Z

1

Maybe its just that easy:

CREATE VIEW apartments AS (
    SELECT 
    building.id_building, 
    apartment.floor,
    count(apartment.id_apartment) as apartments_per_floor
FROM 
    apartment,building 
WHERE 
    building.id_building = apartment.id_building 
GROUP BY apartment.floor, building.id_building, apartment.floor
ORDER BY 1,2
);

SELECT * FROM apartments;

answered Jul 27, 2017 at 9:04

Ben H

4455 silver badges15 bronze badges

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

1 Comment

GuiOm Clair Over a year ago

Indeed! Now I just need to figure out how to present that properly in QGIS. Thanks :)

Collectives™ on Stack Overflow

PostgreSQL 9.5 Multiple SELECT in SELECT

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related