PostgreSQL SELECT null values from two tables

Question

I have problem to solve.

I have two tables, institutions and documents, they joins throught documents owner_id and institution id:

institution

 id | name
----+-----
  1 | a
  2 | b
  3 | c
  4 | d

and

documents

   id | owner | value
  ----+-------+------
    1 |     1 | xxx
    2 |     1 | yyy
    3 |     1 | yyy
    4 |     3 | xxx
    5 |     3 | xxx
    6 |     4 | yyy

And I need to count, how many values has each name, this kind of result:

 name | count(total) | count(xxx) | count(yyy)
------+--------------+------------+------------
 a    |            3 |          1 |          2
 b    |            0 |          0 |          0
 c    |            2 |          2 |          0
 d    |            1 |          0 |          1

I have tried this query:

SELECT 
    a.name,
    (a.xxx + b.yyy) as total,
    a.xxx,
    b.yyy
FROM
    (SELECT count(documents.id) as xxx,
        institution.name
    FROM 
        documents, institution
    WHERE 
        documents.owner_id = institution.id and 
        documents.value = 'xxx'
    GROUP BY 
        institution.name) as a,
    (SELECT count(documents.id) as yyy,
        institution.name
    FROM 
        documents, 
        institution
    WHERE 
        documents.owner_id = institution.id and 
        documents.value = 'yyy'
    GROUP BY 
        institution.name) as b
WHERE 
a.name = b.name
ORDER BY 
a.name

But it returns only those rows, where xxx and yyy is not epmty, this kind of:

 name | count(total) | count(xxx) | count(yyy)
------+--------------+------------+------------
 a    |            3 |          1 |          2

I'm missing these rows:

 name | count(total) | count(xxx) | count(yyy)
------+--------------+------------+------------
 b    |            0 |          0 |          0
 c    |            2 |          2 |          0
 d    |            1 |          0 |          1

because it contains empty data. Thanks in advance.

Julius

Ilia Maskov · Accepted Answer · 2015-04-16 08:49:45Z

1

It can be easier, try this solution

SELECT i.name,
       COUNT(*) total,
       COUNT(CASE d.value WHEN 'xxx' THEN 1 ELSE NULL END) x_cnt,
       COUNT(CASE d.value WHEN 'yyy' THEN 1 ELSE NULL END) y_cnt
FROM institution i
LEFT JOIN documents d ON d.owner = i.id
GROUP BY i.name

answered Apr 16, 2015 at 8:49

Ilia Maskov

1,91816 silver badges27 bronze badges

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

Comments

Frank Heikens · Accepted Answer · 2015-04-16 09:05:43Z

0

In addition to agent5566: As of version 9.4 you could use FILTER instead of a CASE

SELECT  institution.name, 
    COUNT(documents.value) AS cnt_total, 
    COUNT(documents.value) FILTER(WHERE documents.value = 'xxx') AS cnt_xxx,
    COUNT(documents.value) FILTER(WHERE documents.value = 'yyy') AS cnt_yyy
FROM    institution
    LEFT JOIN documents ON documents.owner = institution.id
GROUP BY
    institution.name
ORDER BY
    institution.name;

answered Apr 16, 2015 at 9:05

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Collectives™ on Stack Overflow

PostgreSQL SELECT null values from two tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related