CODEWITHSUNDEEP
postgresql
Doesntmeananything
Doesntmeananything

Reputation: 83

Filter one table based on another in Postgres

I have two tables with the following columns:

Authors

id | name | age

Books

authorId | title | pages

I need to somehow get top five of the author names who have books with more than 200 pages. I experimented with some types of joins and subqueries and this was my best shot:

SELECT 
    author.name, 
    (SELECT COUNT(*) FROM books WHERE pages > 200 AND 'books.authorId' = 'author.id') AS PageCount 
FROM 
    authors AS author 
ORDER BY 
    PageCount DESC

Unfortunately, it returns PageCount as 0.

What would be the way to get the needed result?

Upvotes: 2

Views: 2239

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

You could use an inner join, count, and group by instead of a subquery:

    SELECT  au.name, COUNT(*) PageCount
    FROM    authors AS au
    INNER JOIN books ON  "books"."authorId" = au.id 
    WHERE  books.pages > 200 
    GROUP BY au.name
    ORDER BY PageCount DESC

Don't use single quotes around column names. If you need to, use backticks instead. Single quotes are for literal strings, not for column names.

Upvotes: 2

Related Questions