[SOLVED] What is the correct way to count a record with two rows with two different values, my having clause is not working

What is the correct way to count a record with two rows with two different values, my having clause is not working

I'm trying to count a book in the database where that belong to different locations.

For example the book "Interview with the Vampire" by Anne Rice exist in both library code of 40 and 30 (two locations).

Book Title                          Location Number 

"Interview with the vampire"        30 (Hudson)
"Interview with the vampire"        40 (Mayfield).

What is the best way to find any book tiles with more than 1 location number.

This is what I have so far, but it's not returning the book tiles with two different location.

select book_title, location_code
from books
group by book_title, location_code
having count(location_code) > 2;

Solution

Assuming your RDBMS supports window functions, you can do a windowed count in a sub-query to determine which books to display, then query their details.

with cte as (
    select book_title, location_code,
        count(*) over (partition by book_title) BookCount
    from books
)
select book_title, location_code
from cte
where BookCount >= 2;

Note: As said in the comments you need >= 2 (or > 1).