在多个表上对count / group应用聚合函数

I Have 2 tables

我有2张桌子

table1 (sailors):

table1（水手）：

id_sailor   name 
1       Barondeau   
2       Vighetti

table2 (voyages):

table2（航程）：

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 –

n是特定船上水手的航程数量 -

   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..."

在hsqldb 1.8中，我有这个错误“不在聚合函数或group by子句中：1b6128f ...”

3 个解决方案

#1

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

你需要通过“table2”添加组。你的GROUP BY子句中的“boat”看起来很好。

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

instead of

代替

group by "table2"."name"

#2

Appears to simple "group by" based query

看似简单的“分组依据”查询

select
      v.boat, s.name, count(*) n
from voyages v
innner join sailors s on v.id_sailor = s.id_sailor   
group by
      v.boat, s.name

The important point to note here is that ALL selected columns NOT USING aggregate functions {such as COUNT()}* should be listed in the group by clause.

这里需要注意的重点是，所有选定的列不使用聚合函数{例如COUNT（）} *应列在group by子句中。

#3

just need to add group by table2.boat.

只需要通过table2.boat添加组。

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

#1