jeudi 26 mars 2015

oracle database - sql for counting the columns with the same id with the one in current row


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!





Aucun commentaire:

Enregistrer un commentaire