SQL: Count Group

PeanutGallery

Well-known member
SQLite
DB: Bible
Columns: Book, Chapter, Verse, Scripture

I need to create a view, which displays:
a) number of chapters in each book,
b) number of verses in each chapter.

Do I need to create two views, or can it be displayed in one view?
 
So far:
SELECT
Book, Chapter, count (Verse) as verses
FROM
Bible
GROUP BY Book, Chapter

Need to get Totals of each Books, Chapters, Verses
 
SELECT
Book, COUNT(Chapter) AS TotChap
FROM
Bible
GROUP BY Book ;

I've got it separately; only solution I could come up with. But, tis enough twill do.
 
SELECT
Book, COUNT(Chapter) AS TotChap
FROM
Bible
GROUP BY Book ;

I've got it separately; only solution I could come up with. But, tis enough twill do.
FWIW, it's been well over a decade since I did any SQL and have never used SQLlite, but back in the day, my SQL skills were top drawer.

There may be other ways of doing this, but you should be able to get what you want out of a single query using common table expressions (CTE). Looks like SQLite supports them via the WITH clause. CTEs are a big game changer. Once you learn how to use them, it'll make simple even complex queries - even queries much more complex than what you're doing here.

Just place each of your separate queries into its own CTE. Then reference the results in the main SELECT that follows.

You'll have to work out the syntax on your own, but if you need help with understanding the concept, just ask.

Let me know if this helps.
 
Last edited:
FWIW, it's been well over a decade since I did any SQL and have never used SQLlite, but back in the day, my SQL skills were top drawer.

There may be other ways of doing this, but you should be able to get what you want out of a single query using common table expressions (CTE). Looks like SQLite supports them via the WITH clause. CTEs are a big game changer. Once you learn how to use them, it'll make simple even complex queries - even queries much more complex than what you're doing here.

Just place each of your separate queries into its own CTE. Then reference the results in the main SELECT that follows.

You'll have to work out the syntax on your own, but if you need help with understanding the concept, just ask.

Let me know if this helps.
Thanks; I can live with two separate queries, and save them as views; I can always use a report writer if need be.
 
Back
Top