Getting the count of subcategories in a table grouped by categories and subcategories in `SQLite`

Question

I have the following Loans table:

EmployeeId  LoanCatId   LoanSubCatId    LoanDate
------------------------------------------------
1           4           1               19990125
3           3           2               20101210
6           1           1               19910224
4           4           2               20120219
1           3           1               19920214
2           4           2               19930614
1           3           2               19840705
6           1           1               20030917
5           1           1               19900204
3           1           2               20181113

where the fields EmployeeId, LoanCatId, and LoanSubCatId reference the ID of the following tables, as foreign keys, respectively:

Table Employees:

EmployeeId  Name
------------------
1           John
2           Jack
3           Alex
4           Fred
5           Danny
6           Russel

Table LoanCategories:

CategoryId  CategoryName
------------------------
1           CA
2           CB
3           CC
4           CD

Table LoanSubCategories:

CategoryId  CategoryName
------------------------
1           SCA
2           SCB

I'm trying to get the following table by specifying a LoanDate for example, '19990125' (the first row in the Loans table):

CategoryName    SubCategoryName Count
-------------------------------------
CA              SCA             0
CA              SCB             0
CB              SCA             0
CB              SCB             0
CC              SCA             0
CC              SCB             0
CD              SCA             1
CD              SCB             0

The database is SQLite. Thanks in advance.

Answer 1

You need a CROSS join of LoanCategories and LoanSubCategories to get all the combinations of categories and subcategories and a LEFT join to Loans for the specific date that you want.
Finally, you must aggregate for each category/subcategory:

SELECT c.CategoryName, 
       s.CategoryName AS SubCategoryName,
       COUNT(l.LoanDate) AS Count
FROM LoanCategories c CROSS JOIN LoanSubCategories s
LEFT JOIN Loans l ON l.LoanCatId = c.CategoryId AND l.LoanSubCatId = s.CategoryId AND l.LoanDate = '19990125'
GROUP BY c.CategoryId, s.CategoryId;

See the demo.