'for each output of a select statement, execute another query

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!

sqlsqlitegroup-bycountleft-join


Solution 1:[1]

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:

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Kombajn zbo?owy

Related Questions