list column with count aggregation function

Question

Imagine these tables structure

Organization table

 ===================== 
| id |      name      |
-----+----------------+
| 1  | Organization 1 |
 =====================

Place table

 ================================= 
| id |   name   | organization_id |
-----+----------+-----------------+
| 1  | Place 1  |        1        |
-----+----------+-----------------+
| 2  | Place 2  |        1        |
-----+----------+-----------------+
| 3  | Place 3  |        1        |
 =================================

Profile table

 ============= 
|  username   |
--------------
| [email protected] |
--------------
| [email protected] |
 =============

Visitedplace table

 ================================================ 
| id | place_id | profile_username | visiteddate |
-----+----------+---------------- -+-------------+
| 1  |     1    |   [email protected]    | 2017-01-01  |
-----+----------+------------------+-------------+
| 2  |     2    |   [email protected]    | 2017-02-01  |
-----+----------+------------------+-------------+
| 3  |     1    |   [email protected]    | 2017-01-15  |
 ================================================

Next are sql statments for creating tables and inserting data into it

-- ----------------------------
-- Table structure for organization
-- ----------------------------
DROP TABLE IF EXISTS "organization";
CREATE TABLE "organization" (
"id" int4 NOT NULL,
"name" varchar(255) COLLATE "default" NOT NULL
) WITH (OIDS=FALSE);
-- ----------------------------
-- Records of organization
-- ----------------------------
BEGIN;
INSERT INTO "organization" VALUES ('1', 'Organization 1');
COMMIT;
-- ----------------------------
-- Table structure for place
-- ----------------------------
DROP TABLE IF EXISTS "place";
CREATE TABLE "place" (
"id" int4 NOT NULL,
"name" varchar(255) COLLATE "default" NOT NULL,
"organization_id" int4 NOT NULL
)WITH (OIDS=FALSE);
-- ----------------------------
-- Records of place
-- ----------------------------
BEGIN;
INSERT INTO "place" VALUES ('1', 'Place 1', '1');
INSERT INTO "place" VALUES ('2', 'Place 2', '1');
INSERT INTO "place" VALUES ('3', 'Place 3', '1');
COMMIT;
-- ----------------------------
-- Table structure for profile
-- ----------------------------
DROP TABLE IF EXISTS "profile";
CREATE TABLE "profile" (
"username" varchar(255) COLLATE "default" NOT NULL
)WITH (OIDS=FALSE);
-- ----------------------------
-- Records of profile
-- ----------------------------
BEGIN;
INSERT INTO "profile" VALUES ('[email protected]');
INSERT INTO "profile" VALUES ('[email protected]');
COMMIT;
-- ----------------------------
-- Table structure for visitedplace
-- ----------------------------
DROP TABLE IF EXISTS "visitedplace";
CREATE TABLE "visitedplace" (
"id" int4 NOT NULL,
"place_id" int4 NOT NULL,
"profile_username" varchar(255) COLLATE "default" NOT NULL,
"visiteddate" date NOT NULL
)WITH (OIDS=FALSE);
-- ----------------------------
-- Records of visitedplace
-- ----------------------------
BEGIN;
INSERT INTO "visitedplace" VALUES ('1', '1', '[email protected]', '2017-02-24');
COMMIT;
-- ----------------------------
-- Alter Sequences Owned By 
-- ----------------------------
-- ----------------------------
-- Primary Key structure for table organization
-- ----------------------------
ALTER TABLE "organization" ADD PRIMARY KEY ("id");
-- ----------------------------
-- Primary Key structure for table place
-- ----------------------------
ALTER TABLE "place" ADD PRIMARY KEY ("id");
-- ----------------------------
-- Primary Key structure for table profile
-- ----------------------------
ALTER TABLE "profile" ADD PRIMARY KEY ("username");
-- ----------------------------
-- Primary Key structure for table visitedplace
-- ----------------------------
ALTER TABLE "visitedplace" ADD PRIMARY KEY ("id");
-- ----------------------------
-- Foreign Key structure for table "place"
-- ----------------------------
ALTER TABLE "place" ADD FOREIGN KEY ("organization_id") REFERENCES
"organization" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
------------------------------
-- Foreign Key structure for table "visitedplace"
-- ----------------------------
ALTER TABLE "visitedplace" ADD FOREIGN KEY ("profile_username") REFERENCES    "profile" ("username") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "visitedplace" ADD FOREIGN KEY ("place_id") REFERENCES "place" ("id

") ON DELETE CASCADE ON UPDATE CASCADE;

My question is

When i run this next query

select profile.username, count(distinct place.id)
    from profile profile 
    left outer join visitedplace visitedplace on profile.username=visitedplace.profile_username 
    inner join place place on visitedplace.place_id=place.id
    where place.organization_id=1
    group by profile.username

it return next result

 ===================== 
|  username   | count |
 -------------+-------
| [email protected] |   2   |
 =====================

but i am expecting next result

 ===================== 
|  username   | count |
 -------------+-------
| [email protected] |   2   |
 -------------+-------
| [email protected] |   0   |
 =====================

So how can i do the query to return me what i am expecting?

I am using Postgres

Are you really using all those (somewhat) outdated versions? — user330315
– user330315, Commented Feb 24, 2017 at 22:37
@a_horse_with_no_name can't got ur question, can u clarify pls, thnx — mibrahim.iti
– mibrahim.iti, Commented Feb 24, 2017 at 22:41

Gordon Linoff · Accepted Answer · 2017-02-24 22:35:49Z

1

Use are using left join. So, all joins should be outer joins (after the first one). And, you have to be careful about the where clause:

select p.username, count(distinct place.id)
from profile p left outer join
     visitedplace vp
     on p.username = vp.profile_username left join
     place pl
     on vp.place_id = pl.id and pl.organization_id = 1
group by p.username;

If you are not careful, then the outer join turns into an inner join.

answered Feb 24, 2017 at 22:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

list column with count aggregation function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related