double group by

zaq34kh6 于 2021-08-13 发布在 Java

关注(0)|答案(1)|浏览(427)

我有三张相连的table。

select * 
from Ordering 
inner join Client on client.id=ordering.client_id 
inner join Dish on ordering.dish_id=dish.id

我想要一个总数 quantity 列和分组依据 name_dish 后来呢 client_id .
现在我做到了，但还不够（一个客户）：

select client.familyName, client.name, client.phone, sum(ordering.quantity),dish.name_dish 
from Ordering 
inner join Client on client.id=ordering.client_id 
inner join Dish on ordering.dish_id=dish.id 
where ordering.client_id=1 
group by (dish.name_dish)

sql Join group-by sum

来源：https://stackoverflow.com/questions/62094740/double-group-by-in-multiple-tables

1条答案

按热度按时间

oxf4rvwz1#

从现有查询开始，您需要将 select 中不在聚合函数内的列 group by 子句（即所有列除外 quantity ). 我还建议包括 client 以及 dish 表，如果有两个客户（分别。菜）有相同的名字。
考虑以下聚合查询：

select c.family_name, c.name, c.phone, sum(o.quantity) sum_quantity, d.name_dish
from ordering o
inner join client c on c.id = o.client_id 
inner join dish d on o.dish_id = d.id
group by c.client_id, c.family_name, c.name, c.phone, d.id, d.name_dish

有些数据库（实际上是postgres）只满足于表中两个表的主键 group by 条款：

select c.family_name, c.name, c.phone, sum(o.quantity) sum_quantity, d.name_dish
from ordering o
inner join client c on c.id = o.client_id 
inner join dish d on o.dish_id = d.id
group by c.client_id, d.id

赞(0）回复(0）举报 2021-08-13

我来回答

double group by

1条答案

相关问题

热门标签

最新问答