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

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.

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.<br/>
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.<br/>

Use a CROSS JOIN on the categories tables to obtain all possible combinations, and right join to combine the data, and conditional aggregation to obtain the desired outcome.

select s.catName, s.subCatName, count(case when l.LoanDate = 19990125 then 1 end) as count_
from Loans l
inner join Employees e on e.EmployeeId = l.EmployeeId 
right join (
  select c.CategoryId as catId, c.CategoryName as catName, s.CategoryId as subCatId, s.CategoryName as subCatName
  FROM LoanCategories c 
  CROSS JOIN LoanSubCategories s 
) as s on s.catId = l.LoanCatId and s.subCatId = l.LoanSubCatId
group by s.catId, s.subCatId

Demo here