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

admin管理员组

文章数量:1244214

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;

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;

• sql
• group-by
• rdbms
• having

Share Improve this question Follow edited yesterday Yuan asked Feb 17 at 17:11 YuanYuan 522 bronze badges 5

• Your question is similar to stackoverflow/questions/9574851/return-all-duplicate-rows – Bart McEndree Commented Feb 17 at 17:15
• 4 Try this query: SELECT book_title FROM books GROUP BY book_title HAVING COUNT(DISTINCT location_code) > 1;, grouping only by book_title and then counting distinct location codes? – Tushar Shahi Commented Feb 17 at 17:18
• 2 You're grouping by location code, so the having clause will never return anything. Add the count(distinct...) to your output and remove the having clause and I think you'll understand. – Andrew Commented Feb 17 at 17:44
• 2 And you probably want ">=2" or ">1" – Andrew Commented Feb 17 at 17:44
• Please tag your RDBMS – Dale K Commented Feb 17 at 18:46

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 1

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).

WITH T0 AS (
    SELECT DISTINCT book_title, location_code
    FROM   books
), T1 AS (
    SELECT book_title
    FROM   T0
    GROUP  BY book_title
    HAVING COUNT(*) > 1
)
SELECT T0.*
FROM   T0
       JOIN T1
           ON T0.book_title = T1.book_title;

1. First query (T0) to eliminate duplicates row
2. Second query to find title that have more than 1 differant location
3. Last query to get the result

Converting my comment into answer:

---

Try this query:

SELECT book_title FROM books GROUP BY book_title HAVING COUNT(DISTINCT location_code) > 1;

Here we group only by book_title. Later we count the distinct location_codes using the DISTINCT keyword.

Playground

本文标签：