CODEWITHSUNDEEP
sql
Biver
Biver

Reputation: 23

How to count occurrences in sql

I would like to count how many times an id is present in a table, then print the title associated with this id from another table (next to the amount of occurrences). I also want to only return the top 10 in descending order.

I could only manage to return the total number of occurrences

Upvotes: 0

Views: 113

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You are missing GROUP BY:

SELECT b.title, b.book_id,
       COUNT(*)
FROM books b INNER JOIN
     students_books sb
     ON b.book_id = sb.book_id
GROUP BY b.title, b.book_id
ORDER BY COUNT(*) DESC
LIMIT 10;

I also added table aliases. These generally make the query easier to write and to read.

Upvotes: 1

Related Questions