List grouped and filtered data

Question

List grouped and filtered data

Navigation

#1 by (2 votes)

2

I am doing a query in PUBS, the database that is used in the MSDN tutorials for SQL Server, and I can not find a way to do it.

The two tables I use are publishers and titles that have published book information and their publishers.

I basically want a query that lists the publishers who edited at least 5 books.

I thought of doing a conditional SQL Server query (IF ... ELSE), but I do not know yet how I will elaborate the conditional expression!

I made a query to show the id's of the publishers and the amount of publications made by each one, 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 do not 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 this case).

sql query select

asked by anonymous 07.07.2014 / 19:28

1 answer

Maintenance and retrieval of service URLs Setting a Field for a Proxy

score 2 · Accepted Answer

Use the HAVING clause to return 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