Recursive join count - Oracle

Question

I have this Oracle db structure (something like forum, ok):

CREATE TABLE sections
(
    section_id           INTEGER NOT NULL ,
    parent_section       INTEGER NULL ,
    section_name         VARCHAR2(256) NOT NULL ,
    section_desc         VARCHAR2(1024) NULL 
);

CREATE TABLE topics
(
    topic_id             INTEGER NOT NULL ,
    topic_name           VARCHAR2(256) NOT NULL ,
    section_id           INTEGER NOT NULL
);

CREATE TABLE messages
(
    msg_id               INTEGER NOT NULL ,
    msg_text             CLOB NOT NULL ,
    topic_id             INTEGER NULL ,
    msg_date             DATE NOT NULL ,
    user                 INTEGER NOT NULL
);

As you can see, sections have hierarchical structure (by parent_section column).

I want to make two queries:

1. Get topics count in section by section id, considering all child sections.
2. Same with messages instead of topics.

Ok, I'm trying to write first query, but even it doesn't work (return wrong count without errors), I have no idea why:

SELECT si, t.TOPIC_ID, COUNT(*)
    FROM (
        SELECT s.SECTION_ID si
            FROM SECTIONS s
        START WITH SECTION_ID = :sectionId
        CONNECT BY PRIOR SECTION_ID = PARENT_SECTION
    )
LEFT JOIN TOPICS t ON t.SECTION_ID = si
GROUP BY t.TOPIC_ID, si

EDIT: While I'm setting up SQL Fiddle, I got some solution.

Firstly, we should use INNER JOIN instead of LEFT JOIN, because we don't need records with null topic_id.

Secondly, query with COUNT() was wrong, it just returns same records with additional column COUNT with 1 value. Now I just use SUM(1):

SELECT SUM(1)
  FROM (
    SELECT s.SECTION_ID si
      FROM SECTIONS s
    START WITH SECTION_ID = :sectionId
    CONNECT BY PRIOR SECTION_ID = PARENT_SECTION
  )
INNER JOIN TOPICS t ON t.SECTION_ID = si
GROUP BY 1

But it still not ideal solution, if we pass section without topics as START WITH SECTION_ID = argument, we will get nothing as answer. It requires additional check in program for prevent NullPointerException.

Maybe someone know, how can we get 0 as answer in this case?

SQLFiddle: http://sqlfiddle.com/#!4/8c093/21

Answer 1

Ok, I got it.

We should just remove GROUP BY and use Oracle NVL function, which can replace null value by zero.

SELECT NVL(SUM(1), 0)
  FROM (
    SELECT s.SECTION_ID si
      FROM SECTIONS s
    START WITH SECTION_ID = :sectionId
    CONNECT BY PRIOR SECTION_ID = PARENT_SECTION
  )
INNER JOIN TOPICS t ON t.SECTION_ID = si

EDIT: COUNT(*) without GROUP BY and NVL also a solution:

SELECT COUNT(*)
  FROM (
    SELECT s.SECTION_ID si
      FROM SECTIONS s
    START WITH SECTION_ID = :sectionId
    CONNECT BY PRIOR SECTION_ID = PARENT_SECTION
  )
INNER JOIN TOPICS t ON t.SECTION_ID = si