Counting and grouping over multiple OUTER JOINs I'm creating a view of advertisement logs (impressions, clicks, and clicks per impression). I have a simple table structure and some working queries but I'm having some trouble composing these into a single query that I can use as a view (not a materialized view, as this will be real-time data).

The tables are:

CREATE TABLE advert
(
  id integer NOT NULL PRIMARY KEY
);

CREATE TABLE advert_event
(
  code CHAR(1) NOT NULL PRIMARY KEY
);

CREATE TABLE advert_log
(
  advertisement integer NOT NULL REFERENCES advert(id),
  event_code CHAR(1) NOT NULL REFERENCES advert_event(code)
);


And some sample data that covers all possible cases:

INSERT INTO advert VALUES (1);
INSERT INTO advert VALUES (2);
INSERT INTO advert VALUES (3);
INSERT INTO advert VALUES (4);

INSERT INTO advert_event VALUES ('I'); -- Impression
INSERT INTO advert_event VALUES ('C'); -- Click

INSERT INTO advert_log VALUES (1, 'I');
INSERT INTO advert_log VALUES (1, 'C');
INSERT INTO advert_log VALUES (2, 'I');
INSERT INTO advert_log VALUES (2, 'I');
INSERT INTO advert_log VALUES (2, 'C');
INSERT INTO advert_log VALUES (3, 'I');
INSERT INTO advert_log VALUES (3, 'I');


For references, here is a set of the things I want to count in advert_log:

Query A.

SELECT * FROM advert,advert_event;


Result A.

 id | code
----+------
  1 | I
  1 | C
  2 | I
  2 | C
  3 | I
  3 | C
  4 | I
  4 | C
(8 rows)


Event counts by advertisement:

Query B.

SELECT DISTINCT advertisement,event_code,COUNT(*) OVER (PARTITION BY advertisement,event_code) FROM advert_log;


Result B.

 advertisement | event_code | count
---------------+------------+-------
             1 | I          |     1
             1 | C          |     1
             2 | I          |     2
             2 | C          |     1
             3 | I          |     1
(5 rows)


For any individual advertisement, the correct counts can be obtained by queries like:

Query C1.

SELECT COUNT(*) FROM advert_log WHERE advertisement=4 AND event_code='I';
 count
-------
     0
(1 row)


Query C2.

SELECT COUNT(*) FROM advert_log WHERE advertisement=4 AND event_code='C';
 count
-------
     0
(1 row)


Of course, my earlier query excludes zero counts, so it doesn't catch either of the above two cases.

Ultimately, what I am attempting to do is to turn the above numbers into the following, using clicks (the 'C' entries) divided by impressions (the 'I' entries) to derive the cpi column:

 advertisement | impressions | clicks | cpi
---------------+-------------+--------+-----
             1 |           1 |     1  | 1.0
             2 |           2 |     1  | 0.5
             3 |           1 |     0  | 0.0
             4 |           0 |     0  | 0.0 &lt;- or NULL, NaN, 1.0, ...


My initial approach was to create a view for queries C1 and C2, and to call that function from a view based on Query A.

I suspect there is a simpler way to accomplish my goal with a single query.
I was able to find a solution while I was composing the question, but I've decided to post both the question and answer in case it helps someone else in the future.
Alternatively, if anyone has a simpler or better-performing solution to this problem, I would be happy to hear it.

I managed to solve my NULL-counting problem by using a CROSS JOIN prior to the OUTER JOIN:

SELECT * FROM advert CROSS JOIN advert_event LEFT OUTER JOIN advert_log ON advert_log.advertisement=advert.id AND advert_log.event_code=advert_event.code;

 id | code | advertisement | event_code
----+------+---------------+------------
  1 | I    |             1 | I
  1 | C    |             1 | C
  2 | I    |             2 | I
  2 | I    |             2 | I
  2 | C    |             2 | C
  3 | I    |             3 | I
  3 | I    |             3 | I
  3 | C    |               |
  4 | I    |               |
  4 | C    |               |
(10 rows)


The above gave me the intermediate table I needed. Adding grouping and counting, I finally get the numbers I'm looking for:

SELECT advert.id,advert_event.code,COUNT(advert_log.advertisement) FROM advert CROSS JOIN advert_event LEFT OUTER JOIN advert_log ON advert_log.advertisement=advert.id AND advert_log.event_code=advert_event.code GROUP BY advert.id,advert_event.code;

 id | code | count
----+------+-------
  1 | C    |     1
  1 | I    |     1
  2 | C    |     1
  2 | I    |     2
  3 | C    |     0
  3 | I    |     2
  4 | C    |     0
  4 | I    |     0
(8 rows)


Finally, using two sub-SELECTs (one for 'I', one for 'C'), I composed a query to obtain the counts by advertisement:

CREATE VIEW advertisement_dashboard AS
SELECT a.id,i.impressions,c.clicks,c.clicks::float/greatest(i.impressions, 1) AS cpi FROM advert a,
(
  SELECT advert.id,COUNT(advert_log.advertisement) AS impressions FROM advert CROSS JOIN advert_event LEFT OUTER JOIN advert_log ON advert_log.advertisement=advert.id AND advert_log.event_code=advert_event.code
GROUP BY advert.id,advert_event.code HAVING code='I'
) i,
(
  SELECT advert.id,COUNT(advert_log.advertisement) AS clicks FROM advert CROSS JOIN advert_event LEFT OUTER JOIN advert_log ON advert_log.advertisement=advert.id AND advert_log.event_code=advert_event.code
GROUP BY advert.id,advert_event.code HAVING code='C'
) c WHERE i.id=a.id AND c.id=a.id ORDER BY a.id ASC;

SELECT * FROM advertisement_dashboard;

 id | impressions | clicks | cpi
----+-------------+--------+-----
  1 |           1 |      1 |   1
  2 |           2 |      1 | 0.5
  3 |           2 |      0 |   0
  4 |           0 |      0 |   0
(4 rows)