This is the sort of problem you encounter when your data is not properly normalized. You have the "student" data replicated across three separate tables, rather than having one table for students and related tables for the associated records.
And what's up with those table names? Calling your tables
table1
,
table2
, etc. is a
terrible idea. How is anyone looking at your code or your database supposed to understand what data is stored where? And yes, that includes you when you come back to this code in a month's time. Give your tables meaningful names to reflect the data they contain.
As a hacky workaround, try something like this:
WITH cteStudents As
(
SELECT ID, [Name] FROM table1
UNION SELECT ID, [Name] FROM table2
UNION SELECT ID, [Name] FROM table3
)
SELECT
S.ID,
S.[Name],
(
SELECT Sum(fee)
FROM table1 As T
WHERE T.ID = S.ID
And T.[Name] = S.[Name]
And T.date1 >= '2021-01-01'
And T.date2 < '2021-02-01'
) As Count1,
(
SELECT Sum(fee)
FROM table2 As T
WHERE T.ID = S.ID
And T.[Name] = S.[Name]
And T.date1 >= '2021-01-01'
And T.date2 < '2021-02-01'
) As Count2,
(
SELECT COUNT(StudentFee)
FROM table3 As T
WHERE T.ID = S.ID
And T.[Name] = S.[Name]
And ISDATE(T.InRange) = 1
And T.Paid >= '2021-01-01'
And T.Paid < '2021-02-01'
) As Count3,
(
SELECT COUNT(StudentFee)
FROM table3 As T
WHERE T.ID = S.ID
And T.[Name] = S.[Name]
And T.InRange Is Not Null
And T.Paid >= '2021-01-01'
And T.Paid < '2021-02-01'
) As Count4
FROM
cteStudents As S
;