Java DB, How to count row in Union select

Question

i want to know how to count the row of the union in Java DB of this select

Select R.ID , R.CAPACITE from ASSISTE  A
join REPRESENTATION R on R.ID = A.IDREPRESENTATION
where R.ID = 7
union all
SELECT R.id ,R.CAPACITE FROM BILLET B 
    join REPRESENTATION R on R.ID = B.IDREPRESENTATION
    where R.ID = 7  

i tried something like

Select count(*) from (Select R.ID , R.CAPACITE from ASSISTE  A
    join REPRESENTATION R on R.ID = A.IDREPRESENTATION
    where R.ID = 7
union all
SELECT R.id ,R.CAPACITE FROM BILLET B 
    join REPRESENTATION R on R.ID = B.IDREPRESENTATION
    where R.ID = 7  )

but i got this error!

Error code 30000, SQL state 42X01: Erreur de syntaxe : Encountered "<EOF>" at line 7, column 21.
Line 2, column 1

please Help :D

Answer 1

I think you forgot to add the alias at the end. Try with something like this:

Select count(*) from (
Select R.ID , R.CAPACITE from ASSISTE  A
    join REPRESENTATION R on R.ID = A.IDREPRESENTATION
    where R.ID = 7
union all
SELECT R.id ,R.CAPACITE FROM BILLET B 
    join REPRESENTATION R on R.ID = B.IDREPRESENTATION
    where R.ID = 7  
) A