Retrieve Total Chapters in each books

I have got the database which has id,chapter_name,chapter_num,verse_num,verse_text,testament as columns.

I would like to retrieve chapterName and the total number of chapters in each book.

I’m using the following query to do so,

select chapter_name, count(chapter_name) from TABLE_NAME group by chapter_num;

Unfortunately, it lists a very big count of chapter names and duplicate chapter names as well. How can I fix this? This is the sample input

id,chapter_name,chapter_num,verse_num,verse_text,testament

 1,   "Genesis",    1,             1,       "text",      0
 1,   "Genesis",    1,             2,       "text",      0
 1,   "Genesis",    1,             3,       "text",      0
 .
 .
 .
 1,   "Revelation",   1,             1,       "text",    0

The output should be,

  `id`, `chapter_name`,`count(chapter_name)`



   1,       'Genesis',       50
   2,        'Exodus'        40
   .
   .

   66       'Revalation '    21

Answer

This is a table of verses with redundant chapter (chapter_num) and book (chapter_name) information. We must first eliminate the redundancy and get a single row for each book and chapter. Then we can count them.

select
  chapter_name,
  count(chapter_num)
from (
  select
    chapter_name,
    chapter_num
  from verses
  group by chapter_name, chapter_num
)
group by chapter_name;

Redundancy indicates a poorly designed schema. There’s three pieces of information, so there should be three tables: book, chapter, verse.

create table books (
  id integer primary key,
  name text not null
);

create table chapters (
  id integer primary key,
  num integer not null,
  book_id integer not null references books(id),

  unique(book_id, num)
);

create table verses (
  id integer primary key,
  num integer not null,
  content text not null,
  chapter_id integer not null references chapters(id),

  unique(chapter_id, num)
);

Now if we want to find how many chapters are in each book…

select
  books.name, count(chapters.id)
from books
join chapters on chapters.book_id = books.id
group by books.id

Or how many verses are in each book…

select
  books.name, count(verses.id)
from books
join chapters on chapters.book_id = books.id
join verses on verses.chapter_id = chapters.id
group by books.id

Try it out.