我需要合并2个查询，但需要对其中的一些部分求和

qc6wkl3g 于 2021-06-17 发布在 Mysql

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

我有两个查询，每个查询将输出2列。他们来自同一张table。
查询1

SELECT MONTH(DateOrdered) AS MONTH,SUM(Downpayment)AS total FROM transaction WHERE YEAR(DateOrdered) = YEAR('2018-12-00') AND Status = 'Ongoing' GROUP BY MONTH(DateOrdered)

MONTH-----total

10------------4590

12------------1497.5

查询2

SELECT MONTH(DateFinish) AS MONTH,SUM(Total-Downpayment)AS total FROM transaction WHERE YEAR(DateFinish) = YEAR('2018-12-00') AND Status = 'Complete' GROUP BY MONTH(DateFinish)

MONTH--------total

5-----------------1147.5

10----------------1647

12----------------1147.5

我要将这两个列合并，并对名为total的两列求和
例如

MONTH------------total

5--------------------1147.5

10--------------------6237

12--------------------2645

我现在所拥有的

SELECT MONTH(DateOrdered) AS MONTH,SUM(Downpayment) FROM transaction WHERE YEAR(DateOrdered) = YEAR('2018-12-00') AND Status = 'Ongoing' GROUP BY MONTH(DateOrdered) UNION SELECT MONTH(DateFinish) AS MONTH,SUM(Total-Downpayment) FROM transaction WHERE YEAR(DateFinish) = YEAR('2018-12-00') AND Status = 'Complete' GROUP BY MONTH(DateFinish)

它显示了

MONTH--------total

10----------------4590

12----------------1497.5

5-----------------1147.5

10----------------1647

12----------------1147.5

mysql

来源：https://stackoverflow.com/questions/53958691/i-need-to-combine-2-queries-but-need-to-sum-some-part-of-them

1条答案

按热度按时间

bpzcxfmw1#

这里使用的高级概念有：1）联合查询和2）子查询
它看起来像下面这样：

Select 
    sum(total) as totalByMonth,
    month
from
(
    SELECT 
        MONTH(DateOrdered) AS MONTH,
        SUM(Downpayment)AS total 
    FROM 
        transaction 
    WHERE 
        YEAR(DateOrdered) = YEAR('2018-12-00') 
        AND Status = 'Ongoing' 
    GROUP BY 
        MONTH(DateOrdered)

    Union

    SELECT 
        MONTH(DateFinish) AS MONTH,
        SUM(Total-Downpayment)AS total 
    FROM 
        transaction 
    WHERE 
        YEAR(DateFinish) = YEAR('2018-12-00') 
        AND Status = 'Complete' 
    GROUP BY 
        MONTH(DateFinish)

) subQuery1

group by
    month

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

我来回答

我需要合并2个查询，但需要对其中的一些部分求和

1条答案

相关问题

热门标签

最新问答