CODEWITHSUNDEEP
sqloracle-databaseplsql
frgtv10
frgtv10

Reputation: 5440

COUNT inside CONNECT BY

I'm having a connect by statement which returns me every project and its childrens.

Now I want to have the amount of returned rows in the SELECT statement so far. What I tried:

SELECT 
      PROJECT_NAME,
      PROJECT_LEVEL,
      count(PROJECT_ID) 
from PROJECTS
where PROJECT_NAME is not null 
      connect by prior PROJECTS.id = PROJECTS.parent_id
      start with PROJECTS.id = 8572435

I'm getting "not a single-group group function".

Adding GROUP BY:

SELECT 
      PROJECT_NAME,
      PROJECT_LEVEL,
      count(PROJECT_ID) 
from PROJECTS
where PROJECT_NAME is not null 
      connect by prior PROJECTS.id = PROJECTS.parent_id
      start with PROJECTS.id = 8572435
GROUP BY PROJECT_NAME, PROJECT_LEVEL

RETURNS me 1 in every row.

Am I doing it wrong?

Upvotes: 0

Views: 111

Answers (1)

user1930857
user1930857

Reputation:

Solution is mentioned in the error itself. Please try using group by clause at the end of sql statement which will solve your problem definately :) You cannot use aggregrate function for 1 column if you selecting multiple columns. You have to use Group By clause for other columns those are not use with aggregrate function :)

SELECT 
      PROJECT_NAME,
      PROJECT_LEVEL,
      count(PROJECT_ID) 
from PROJECTS
where PROJECT_NAME is not null 
      connect by prior PROJECTS.id = PROJECTS.parent_id
      start with PROJECTS.id = 8572435
      Group By PROJECT_NAME, PROJECT_LEVEL

Upvotes: 1

Related Questions