Apply aggregates function on count / group by on multiple tables

Question

I Have 2 tables

table1 (sailors):

id_sailor   name 
1       Barondeau   
2       Vighetti    

table2 (voyages):

id_ voyage     boat       id_sailor
1                Juliette         1
2                Juliette         1
3               La belle          2
4               La Belle          1

How can I make this new table :

n is the number of voyages for a sailor on a specific boat –

   boat     name      n     
   Juliette Barondeau 2
   La Belle Barondeau 1
   La Belle Vighetti  1

What I tried :

  select "table2"."boat", "table1"."name", count("table2"."boat" ) as "n"
  from "table1", "table2" 
  where "table1"."id_sailor" = "table2"."id_sailor"
  group by "table2"."name"
  ;

In hsqldb 1.8, I have this error "Not in aggregate function or group by clause : 1b6128f..."

Answer 1

You need to add group by "table2"."boat" in your GROUP BY clause remaining looks fine.

 group by "table2"."boat","table2"."name"

instead of

group by "table2"."name"