0

I have an oracle database and a few tables and with this query:

select rap.DENUMIRE,a3.ID,op.FK_ID,
    (SELECT COUNT(*) FROM ANEXA3_TABEL1_ROWS) AS count
from ANEXA3_TABEL1_ROWS a3
inner join RAPORTORI_AMB rap on rap.id = ch.FK_ID_RAPORTOR
inner join ANEXA3_TABEL1_OP op on op.FK_ID = a3.ID

the result is:

DENUMIRE    ID  FK_ID   COUNT
SC AAA      720 720     7
SC AAA      720 720     7
SC AAA      720 720     7
SC AAA      741 741     7
SC AAA      720 720     7
SC AAA      720 720     7
SC AAA      742 742     7

and I'd want to select in COUNT the number of columns with FK_ID equals to the ID in the current row. In this example, I'd want the following result:

DENUMIRE    ID  FK_ID   COUNT
SC AAA      720 720     5
SC AAA      720 720     5
SC AAA      720 720     5
SC AAA      741 741     1
SC AAA      720 720     5
SC AAA      720 720     5
SC AAA      742 742     1

Thanks in advance!

  • How do you distinguish between the five records SS AAA 720 720 5? Where's your Primary Key? – Vérace Mar 26 '15 at 9:38
  • It was just a select from other tables - everyone with his PK of course, but irrelevant info. @Dmitry answered my problem, thanks! – WDrgn Mar 26 '15 at 9:43
0
select rap.DENUMIRE,a3.ID, op.FK_ID, count(*) over (partition by op.FK_ID) AS count
  from ANEXA3_TABEL1_ROWS a3
 inner join RAPORTORI_AMB rap on rap.id = ch.FK_ID_RAPORTOR
 inner join ANEXA3_TABEL1_OP op on op.FK_ID = a3.ID
  • with that info, do you know how I can find out if it's the first occurence of that id-match ? – WDrgn Mar 26 '15 at 11:53
  • I think, you need to read about analytic functions: docs.oracle.com/cd/E11882_01/server.112/e41084/… For first occurence, you need row_number function: row_number() over (partition by op.FK_ID order by op.FK_ID). For first occurence function gives 1, for second - 2, and so on. – Dmitriy Mar 26 '15 at 12:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.