Tested in build PGSQL (14300) and MSSQL (14306)
PGSQL:
SELECT projectdet.title AS "Project name",
count(case when (taskstatus.statusid='1') THEN 1 ELSE NULL END) "Open Tasks",
count(case when (taskstatus.statusid='6') THEN 6 ELSE NULL END) "In Progress Tasks",
count(case when (taskstatus.statusid='2') THEN 2 ELSE NULL END) "On Hold Tasks",
count(case when (taskstatus.statusid='3') THEN 3 ELSE NULL END) "Closed Tasks",
count(case when (taskstatus.statusid='4') THEN 4 ELSE NULL END) "Resolved Tasks",
count(taskdet.TASKID ) "Total" FROM ProjectDetails projectdet
LEFT JOIN TaskToProjects projtotaskdet ON projectdet.PROJECTID= projtotaskdet.PROJECTID
LEFT JOIN TaskDetails taskdet ON projtotaskdet.TASKID=taskdet.TASKID
LEFT JOIN TaskToProjects miletotaskdet ON taskdet.TASKID=miletotaskdet.TASKID
LEFT JOIN MileStoneDetails md ON miletotaskdet.MILESTONEID=md.MILESTONEID
LEFT JOIN StatusDefinition taskstatus ON taskdet.STATUSID=taskstatus.STATUSID
WHERE taskdet.MODULE IN ('Milestone', 'Project') group by projectdet.title