for each output of a select statement, execute another query-CodePudding

Please consider this question:

A sqlite db with two tables, and I need to take bookName from one table and generate count on each of the results in another table.

$ sqlite3 Sqlite.db "select bookName from books" 
myBook.1
myBook.2
myBook.3
myBook.4
myBook.5

Tried subQuery, probably wrongly, with wrong results:

sqlite> select count(*) from tags where bookName = (select bookName from books);
753

This is what I am trying to do:

$ sqlite3 SSqlite.db "select bookName from books" | while read a ; do  sqlite3 Sqlite.db "select bookName,count(*) from tags where bookName = \"$a\""; done
myBook.1|753
myBook.2|677
myBook.3|573
myBook.4|656
myBook.5|103

This must be possible much simpler within SQL, any input is much appreciated!

CodePudding user response：

If you want results only for the books in the table tags, then you should group by bookName:

SELECT COUNT(*) counter
FROM tags 
GROUP BY bookName;

If you want results for all the books in the table books then use a LEFT join and aggregation:

SELECT b.bookName, COUNT(t.bookName) counter
FROM books b LEFT JOIN tags t
ON t.bookName = b.bookName 
GROUP BY b.bookName;

CodePudding user response：

If I understand correctly you are looking for grouping query... You almost got that right:

select bookName, count(*) from tags group by bookName;

Depending on your case you might want to join or right join with your other table (books).

Check documentation about: