Mysql子select with SUM() and group by

I am trying to create a query that does a calculation in a subquery that requires the SUM function and a group by. My query returns the error "Subquery returns more than 1 row". Essentially I am trying to return the amount "Due" for each order. If the order total is greater than the sum of total_collected (for that order_id) from the payments table there will be amount due. Here is the query:

我正在尝试创建一个查询，该查询在需要SUM函数和group by的子查询中执行计算。我的查询返回错误“子查询返回多于一行”。本质上，我试图返回每个订单的“到期金额”。如果订单总数大于从付款表中收集的total_collected(对于该order_id)的总和，那么将会有相应的金额。在这里查询:

SELECT o.order_id
     , o.server
     , o.subtotal
     , o.discount
     , o.tax, o.total
     , (SELECT (o.total - SUM(p.total_collected)) 
            from orders o 
            join payments p 
              on o.order_id = p.order_id 
        group by p.order_id) as 'Due'
  FROM orders o 
  join payments p 
    on o.order_id = p.order_id
WHERE...;

I cannot include 'p.order_id' in the sub select because it should only contain one column. I understand why I am getting the error, I just don't know how to get the sub select to only perform the SUM on a per order_id basis.

我不能包括“p。在子选择中order_id，因为它只包含一个列。我理解为什么会出现错误，我只是不知道如何让subselect只在order_id的基础上执行和。

2 个解决方案

#1

Without changing the structure much, I think the subquery is looking at all of the data in the orders/payments tables. I think you need to filter it down to look only at the relevant order_id like so.

在不改变结构的情况下，我认为子查询将查看订单/付款表中的所有数据。我认为您需要将其过滤，以便只查看相关的order_id。

(I also added a SUM around the order total because I am pretty sure that would give a different error without it.)

(我还在订单总数上加了一个和，因为我很确定没有它会产生不同的错误。)

SELECT o.order_id
     , o.server
     , o.subtotal
     , o.discount
     , o.tax
     , o.total
     , (SELECT (SUM(o2.total) - SUM(p.total_collected))
            from orders o2 
            JOIN payments p 
              ON o2.order_id = p.order_id
            WHERE o2.order_id = o.order_id) as 'Due'
    FROM orders o
WHERE...;

Although, if you adjust this so that it uses a join instead of a subquery, I think you will get better performance. Something like this:

虽然，如果您调整它，使它使用连接而不是子查询，我认为您将获得更好的性能。是这样的:

SELECT o.order_id
     , o.server
     , o.subtotal
     , o.discount
     , o.tax
     , o.total
     , o.total - c.Collected AS 'Due'
    FROM orders o
    JOIN (
        SELECT p2.order_id, SUM(p2.total_collected) AS 'Collected'
          FROM payments p2
        GROUP BY p2.order_id) AS c
      ON o.order_id = c.order_id
WHERE...;

#2

You do not need sub-query:

不需要子查询:

SELECT
  o.order_id,
  o.server,
  o.subtotal,
  o.discount,
  o.tax,
  o.total,
  o.total - ifnull(sum(p.total_collected),0) As Due
FROM orders AS o 
LEFT JOIN payments AS p ON o.order_id = p.order_id
WHERE ...
GROUP BY o.order_id

#1