postgres_select_distinct_join_groupby_hstorekey, PostgreSQL

postgres_select_distinct_join_groupby_hstorekey

DROP TABLE IF EXISTS pets;
DROP TABLE IF EXISTS locations;
DROP TABLE IF EXISTS photos;

/* In reality i'm using surrogate keys but I used text here to simplify example */
CREATE TABLE pets(
    id TEXT PRIMARY KEY     NOT NULL
);

CREATE TABLE locations (
    id TEXT PRIMARY KEY     NOT NULL,
    attr hstore
);

CREATE TABLE photos (
    id INT PRIMARY KEY     NOT NULL,
    pet_id TEXT REFERENCES pets(id),
    location_id TEXT REFERENCES locations(id)
);

/* I have 3 pets */
INSERT INTO pets VALUES ('dog');
INSERT INTO pets VALUES ('cat');
INSERT INTO pets VALUES ('parrot');

/* went on holiday to a couple of locations with them */
INSERT INTO locations VALUES ('windmill','"country"=>"Canada"');
INSERT INTO locations VALUES ('bridge','"country"=>"Italy"');

/* And took some pictures */
INSERT INTO photos VALUES (1,'cat','windmill');
INSERT INTO photos VALUES (2,'cat','windmill');
INSERT INTO photos VALUES (3,'cat','bridge');
INSERT INTO photos VALUES (4,'dog','bridge');

/* I want to know how many of my pets (distinct) I took a picture of with breakdown per country*/

/* This gives me the group by location, but it doesn't add up the counts per country */
SELECT COUNT(DISTINCT pets.id),locations.attr -> 'country' as country
FROM pets,photos,locations
WHERE photos.pet_id = pets.id
AND photos.location_id = locations.id
GROUP BY pets.id,locations.attr -> 'country';


run \| edit \| history \| help	0

λ

                                      .NET NoSQL database for rapid development