CODEWITHSUNDEEP
sqloracle-database
Andy Vincent
Andy Vincent

Reputation: 83

Select - by ID of a group - in SQL Oracle

I have this query that selects several columns, groups and assigns IDs to those groups. Now, my final step would be to select a group by ID. But for some reason it doesn't work.

select MYCOLUMN, 
   col2,
   sum(cal_a+col_b),
   col3,
   count(col4) as col4sum,
   DENSE_RANK()OVER(ORDER BY MYCOLUMN) GROUPID
from my_tab
group by  MYCOLUMN, col2, col3
where GROUPID = '2'

What could be wrong, how can I select my group 2?

Upvotes: 0

Views: 1618

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You need to use a subquery or CTE:

select t.*
from (select MYCOLUMN, col2, sum(cal_a+col_b), col3,
             count(col4) as col4sum,
             DENSE_RANK() OVER (ORDER BY MYCOLUMN) as GROUPID
      from my_tab
      group by MYCOLUMN, col2, col3
     ) t
where GROUPID = 2;

Don't use single quotes around integer constants. Only use single quotes for string and date constants.

Upvotes: 1

Related Questions