对子查询结果求和

nwnhqdif 于 2021-06-25 发布在 Mysql

关注(0)|答案(2)|浏览(500)

表格示例：

| date_time           | quantity
-------------------------
| 2017-08-12 09:11:51 | 1
| 2017-08-12 10:12:51 | 1
| 2017-08-12 12:45:51 | 1
| 2017-08-13 11:12:51 | 1
| 2017-08-13 11:12:51 | 1
| 2017-08-13 12:45:51 | 1
| 2017-08-13 13:57:51 | 1
| 2017-08-14 14:23:51 | 1
| 2017-08-14 16:34:51 | 1
| 2017-08-15 16:21:51 | 1
| 2017-08-16 14:31:51 | 1

我可以通过以下查询得到结果：

SELECT DATE(date_time) as date_time
    ,SUM(quantity) as quantity
FROM report
WHERE date_time BETWEEN '' AND  ''
GROUP BY DATE(date_time)
HAVING SUM(quantity) < 3

结果：

| date_time  | quantity
 -------------
| 2017-08-14 | 2
| 2017-08-15 | 1
| 2017-08-16 | 1

我想以后再计算数量。例如：

quantity
---------
| 4

为了实现这一点，我知道我需要将结果用作子查询，以便随后求和。我试过了，但错了：

SELECT SUM(topla) FROM (
  ( SELECT DATE(date_time) as date_time
        ,SUM(quantity) as quantity
    FROM report
    WHERE date_time BETWEEN '' AND  ''
    GROUP BY DATE(date_time)
    HAVING SUM(quantity) < 3 
  ) AS topla
)

我怎样计算数量？

sql mysql

来源：https://stackoverflow.com/questions/48182609/sum-the-sub-query-result

2条答案

按热度按时间

cuxqih211#

只需将topla替换为quantity，并从topla之前的查询中删除'as topla'。

SELECT SUM(quantity) total FROM (
  ( SELECT DATE(date_time) as date_time
        ,SUM(quantity) as quantity
    FROM report
    WHERE date_time BETWEEN '' AND  ''
    GROUP BY DATE(date_time)
    HAVING total < 3 
  )
)

要使用sum函数，必须指定列名而不是表名。

赞(0）回复(0）举报 2021-06-25

8ulbf1ek2#

首先，您必须按列名对数量求和(在这个例子中，我把它命名为sub（数量）
其次，您不需要选择日期。
所以你的问题可能是

SELECT SUM(sub_quantity) AS quantity
FROM
  ( SELECT SUM(quantity) AS sub_quantity
   FROM report
   WHERE date_time BETWEEN '' AND ''
   GROUP BY DATE(date_time)
   HAVING SUM(quantity) < 3) a

赞(0）回复(0）举报 2021-06-25

我来回答

对子查询结果求和

2条答案

相关问题

热门标签

最新问答