SQL SELECT COUNT FILTER
SQL SELECT COUNT FILTER
(OP)
I am currently using the following SQL Statement, which give me Projects and a count of the Child Forms associated to those Projects. However, I only want to return Projects where the Child Form Count is greater than zero (i.e. ChildCount > 0, which obviously doesn't work that way).
SELECT
Project.ProjectID as ID,
Project.ProjectName as Name,
(SELECT COUNT(DISTINCT Form.FormID) FROM Form WHERE
Form.ProjectID=Project.ProjectID) as ChildCount
FROM Project
ORDER BY Name
I have tried putting the (SELECT COUNT(DISTINCT Form.FormID) FROM Form WHERE Form.ProjectID=Project.ProjectID)>0 in the WHERE clause, but that increased my query time from <1 sec to over 9 sec.
Is there another way to achieve what I am trying to do and still have my SQL perform well?
Thanks in advance for your assistance.
SELECT
Project.ProjectID as ID,
Project.ProjectName as Name,
(SELECT COUNT(DISTINCT Form.FormID) FROM Form WHERE
Form.ProjectID=Project.ProjectID) as ChildCount
FROM Project
ORDER BY Name
I have tried putting the (SELECT COUNT(DISTINCT Form.FormID) FROM Form WHERE Form.ProjectID=Project.ProjectID)>0 in the WHERE clause, but that increased my query time from <1 sec to over 9 sec.
Is there another way to achieve what I am trying to do and still have my SQL perform well?
Thanks in advance for your assistance.

Talk To Other Members
RE: SQL SELECT COUNT FILTER
Joins are usually faster than subqueries anyway.
here's a rough try at it
CODE
Project.ProjectID as ID,
Project.ProjectName as Name,
F.childcount
FROM Project
join
select count(*) as ChildCount, formid from Form group by formid having count(*)>0) F
on F.ProjectID=Project.ProjectID
ORDER BY Name
"NOTHING is more important in a database than integrity." ESquared
RE: SQL SELECT COUNT FILTER
CODE
Project.ProjectName As Name,
DerivedTableAlias.FormCount
From Project
Inner Join (
Select Form.ProjectId,
Count(*) As FormCount
From Form
Group By ProjectId
) As DerivedTableAlias
On Project.ProjectId = DerivedTableAlias.ProjectId
If this works for you, and performs well, let me know and I will explain how it works.
-George
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
RE: SQL SELECT COUNT FILTER
Thanks much, that worked great. Now I need to tie in some security to restrict the Projects and Forms and was doing it with a WHERE CLAUSE, but when I add it in my SQL performance again goes from <1 second to 10s.
WHERE Project.ClientId IN (SELECT DISTINCT ClientId FROM STORE INNER JOIN FLAT_HIER_USER_STORE ON FLAT_HIER_USER_STORE.SVC_LOC_ID = STORE.SvcLocId WHERE FLAT_HIER_USER_STORE.USER_COMMON_NAME = 'qatestra')
If I just use the following SQL with security in the WHERE clause, it is less than a second:
SELECT Project.ProjectID as ID, Project.ProjectName as Name
FROM Project WHERE Project.ClientId IN (SELECT DISTINCT ClientId FROM STORE INNER JOIN FLAT_HIER_USER_STORE ON FLAT_HIER_USER_STORE.SVC_LOC_ID = STORE.SvcLocId WHERE FLAT_HIER_USER_STORE.USER_COMMON_NAME = 'qatestra')
ORDER BY Name
Thanks again for all the help.
Ian
RE: SQL SELECT COUNT FILTER
More specifically, it's your use of the IN clause that is causing your performance problems.
-George
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom