Using COUNT in a query and showing rows with count 0

[Former Member]

Hi All,

I have been creating reports that use the COUNT function to calculate the number of documents at each phase, however I am struggling to get a row to show in the report if there are 0 documents at a phase.

For example I can easily create a report that shows:

Project Phase	Count of Projects at this phase
Phase 1	3
Phase 2	5
Phase 4	2

I actually want this to show:

Project Phase	Count of Projects at this phase
Phase 1	3
Phase 2	5
Phase 3	0
Phase 4	2

Does anyone have any advice or example code that would help me achieve this?

Many thanks

Dan

[Former Member · Accepted solution]

Hello Dan,

It cannot be done within the same query block. One way of achieving it is

a) Have 2 blocks in the query with a UNION operator

b) First part of the query will have the existing logic to return Phase Name and Count of Docs. This query will return rows only for phases where documents are there

b) Second part of query (After UNION) will have a sub query that filters out all phases that have documents

Sample query

Existing Query

UNION

select

phase_name,

0 AS COUNT

FROM FCI_PROJECT_PHASES

where phase_name not in (

select distinct phase_name from fci_projects)

Hope this helps

Regards,

Balaji

[Former Member · Accepted solution]

Hi Balaji,

Thank you for the helpful response.

I am struggling to get the second query to work properly, I can not get it to show the project phases that do not currently have a project at that phase.

The query I am using is:

SELECT

T1.DISPLAY_NAME AS PHASE,

0 AS COUNT

0 AS SUM

FROM

<%SCHEMA%>.FCI_PRO_CONFIG_PHASE_SUB T1

LEFT OUTER JOIN <%SCHEMA%>.FCI_PRO_PHASE_CONFIG T2 ON

(T1.PARENT_OBJECT_ID = T2.OBJECTID)

WHERE

T1.INACTIVE = 0 AND

T2.INACTIVE = 0 AND

T1.OBJECTID NOT IN

(

SELECT

DISTINCT CURR_CONFIG_PHASE_OBJECT_ID

FROM

<%SCHEMA%>.FCI_PRO_PROJECTS

WHERE

INACTIVE = 0 AND

IS_TEMPLATE = 0 AND

CONTEXTID=<%CONTEXT(projects.projects)%>

)

Can you see what the issue is with the query?

Many thanks

Dan

[Former Member · Accepted solution]

Hi Dan,

           Please see this query to get null counted.

Select T0.CardCode,ISNULL((Select Count(T1.CardCode)

from OINV T1 Where T0.Cardcode=T1.CardCode Group By T1.CardCode ),0) as 'No. of AR Invoice' from OCRD T0

Order by T0.CardCode

Thanks.

Andy

[Former Member · Accepted solution]

Hello Dan,

Looked into your query. Have added one more criteria to the query and it should work fine

SELECT

T1.DISPLAY_NAME AS PHASE,

0 AS COUNT

0 AS SUM

FROM

<%SCHEMA%>.FCI_PRO_CONFIG_PHASE_SUB T1

LEFT OUTER JOIN <%SCHEMA%>.FCI_PRO_PHASE_CONFIG T2 ON

(T1.PARENT_OBJECT_ID = T2.OBJECTID)

WHERE

T1.INACTIVE = 0 AND

T2.INACTIVE = 0 AND

T1.OBJECTID NOT IN

(

SELECT

DISTINCT CURR_CONFIG_PHASE_OBJECT_ID

FROM

<%SCHEMA%>.FCI_PRO_PROJECTS

WHERE

INACTIVE = 0 AND

IS_TEMPLATE = 0

AND CURR_CONFIG_PHASE_OBJECT_ID IS NOT NULL

AND CONTEXTID=<%CONTEXT(projects.projects)%>

)

Regards,

Balaji