Wilcar Wilcar - 3 years ago 154
SQL Question

Apply aggregates function on count / group by on multiple tables

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 Source

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"
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download