Question 219685 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Join and group by based on a value of a column to filter

Let’s say I have two tables with these data:

author
id | name
---------
1  | John
2  | Jack
3  | James
4  | Bob
5  | Gorge

book
id | author_id | title  | state
-------------------------------------
1  | 1         | book_1 | published
2  | 1         | book_2 | pre-published
3  | 1         | book_3 | published
4  | 1         | book_4 | rejected
5  | 2         | book_5 | published
6  | 3         | book_6 | rejected
7  | 3         | book_7 | pre-published
8  | 4         | book_8 | rejected

What I want is a sql query that gives me each of author’s id and name and number of published book even if they have 0 published. I think it might be an easy query but I stuck in it, what I want is:

id | name   | num_of_published_books
------------------------------------
1  | John   |          2
2  | Jack   |          1
3  | James  |          0
4  | Bob    |          0
5  | Gorge  |          0

I’m able to fetch first two rows John and Jack. and also I am able to group them based on author_id and state. but it was not what I want.

note that I don’t want to use subquery.

Tags:

4

Answers


  1. 0 
    SELECT
  a.id AS author_id,
  a.name,
  COUNT(b.state = 'published' OR NULL) AS num_of_published_books
FROM
  author a
LEFT JOIN
  book b ON a.id = b.author_id
GROUP BY
  a.id, a.name;

    Hope it will help you.

    1. b.state = 'published', we can delete the published books, the result is false(0) or true(1).
    2. OR NULL is to deal with NULL. In my memory, when using OR operator to compare non-null value and NULL value, the result will always be NULL. COUNT will NULL is ignored.
    3. There are two possible results: true(1) OR NULL and false(0) OR NULL, and their results are true(1) and false(0) respectively.
    4. Finally, Count counts the number that is not NULL.
    Login or Signup to reply.
  2. 0 
    SELECT author.id, author.name, 
       COALESCE(COUNT(CASE state  
                         WHEN 'published' THEN 1 
                         ELSE O 
                      END), 0)) as num_of_published_books
FROM   author
       LEFT OUTER JOIN book
          ON author.id = book.author_id
GROUP BY author.id, author.name
    Login or Signup to reply.
  3. 0

    To achieve the desired result, you can use a LEFT JOIN and GROUP BY in your SQL query.
    The LEFT JOIN ensures that all authors from the "author" table are included, even if they have no corresponding records in the "book" table.
    Here’s the SQL query that should give you the expected result:

    SELECT a.id,a.name,
  COALESCE(SUM(CASE WHEN b.state = 'published' THEN 1 ELSE 0 END), 0) AS num_of_published_books
FROM author a LEFT JOIN book b ON a.id = b.author_id
GROUP BY a.id, a.name;

    Check query result http://sqlfiddle.com/#!9/0db9c8a/1/0

    Explanation:
    The query uses a LEFT JOIN to combine the "author" and "book" tables based on the "author_id." It then employs the SUM function with a CASE statement to count the number of published books for each author (1 for ‘published’, 0 for others). COALESCE is used to handle NULL values and replace them with 0 in case an author has no published books.

    Login or Signup to reply.
  4. 0

    One more version using aggregation with FILTER.

    select a.id, min(a.name) as name, 
       count(*) filter (where state = 'published') as num_of_published_books
from author a left outer join book b on a.id = b.author_id
group by a.id;

    Please note that min(a.name) may become simply a.name if author.id is primary key.
    DB Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search