Skip to Content
avatar image
Former Member

Using COUNT in a query and showing rows with count 0

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    May 30, 2013 at 05:07 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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