左联接SQL的总和 [英] Sum on a left join SQL

问题描述

我想加入两个表，让我们说表a和表b.表b到表a的行很多，表b包含价格(实际上是一个购物篮).所以我想要的是表a的所有记录和表b的价格总和.我尝试过

I have two tables I wish to join on lets say table a and table b. Table b has many rows to table a's, table b contains prices (effectively a shopping basket). So what I want is all records from table a and the sum of the price from table b. I have tried

select a.*, sum(b.ach_sell) from bookings a 
left join pricing_line b on b.bookings = a.id

但是，这显然不符合我的期望，最终以所有ach_sell的总和结束(因此返回一条记录).有人会为我提供一个有帮助的解决方案吗?现在我正在以编程方式进行操作，而且我很确定可以在SQL中完成此操作?

However this obviously doesn't do as I wish, it ends up with the total sum of all ach_sell (so one record is returned). Would someone kindly offer me a solution which would help? right now I am doing it programatically and I am pretty sure it could be done in SQL?

推荐答案

您的方向是正确的，只需添加group by子句以分隔a.id，就像这样:

Your direction is right, just add a group by clause to separate the a.id's, something like this:

select a.id, sum(b.ach_sell) 
from bookings a  
  left join pricing_line b 
on b.bookings = a.id 
group by a.id

这篇关于左联接SQL的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！