List grouped and filtered data

Question

I’m performing a query in PUBS, the database that is used in MSDN tutorials for SQL Server, and I can’t find a way to do it.

The two tables I use are the Publishers and the Titles who have the information of the published books and their respective publishers.

Basically, I want a query that lists the publishers that have edited at least 5 books.

I thought about making a conditional query of SQL Server (IF...ELSE), but I don’t know yet how I will elaborate the conditional expression!

I performed a query to show the id’s of the publishers and the amount of publications performed by each, here is the code:

    SELECT COUNT (DISTINCT t.title_id)
    FROM titles t
    INNER JOIN publishers p ON t.pub_id = p.pub_id
    GROUP BY p.pub_id

I don’t know if there is any way to set a variable with the values returned by COUNT and then assign it to a conditional expression (@Variable > 5, in the case).

Answer 1

Use the clause HAVING to return the publishers with more than n titles:

SELECT COUNT(t.title_id)
FROM titles t
INNER JOIN publishers p ON t.pub_id = p.pub_id
GROUP BY p.pub_id
HAVING COUNT(t.pub_id) > 5