如何在mysql中生成季度报表，并按季度求和

ogsagwnx 于 2021-07-27 发布在 Java

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

我想在mysql数据库中生成一个季度报告，并按季度求和。我写了下面的查询，它不工作

SELECT t.name as terminals, 
    sum(bl.barrels_net_qty) as 'IST QTR', 
    sum(bl2.barrels_net_qty) as '2ND QTR', 
    sum(bl3.barrels_net_qty) as '3RD QTR', 
    sum(bl4.barrels_net_qty) as '4TH QTR', 
    sum(coalesce(bl.barrels_net_qty,0)) 
+ sum(coalesce(bl2.barrels_net_qty,0)) 
+ sum(coalesce(bl3.barrels_net_qty,0))
+ sum(coalesce(bl4.barrels_net_qty,0)) as total 
FROM loadingdetails  as l
LEFT JOIN bldetails as bl on bl.operation_no = l.operation_no AND l.year = '2020' AND bl.month = 'January' and bl.month = 'Feburary' and bl.month = 'March'
LEFT JOIN bldetails as bl2 on bl2.operation_no = l.operation_no  and l.year = '2020' AND bl2.month = 'April' and bl2.month = 'May' and bl2.month = 'June'  AND l.month = 'April' and bl2.month = 'May' and bl2.month = 'June'
LEFT JOIN bldetails as bl3 on bl3.operation_no = l.operation_no  and l.year = '2020' AND bl3.month = 'July' and bl3.month = 'August' and bl3.month = 'September'
LEFT JOIN bldetails as bl4 on bl4.operation_no = l.operation_no and l.year = '2020' AND bl4.month = 'October' and bl4.month = 'November' and bl4.month = 'December'
LEFT JOIN terminals as t on t.id = l.terminal_id
GROUP BY t.name

下面是输出。

sql mysql

来源：https://stackoverflow.com/questions/63172574/how-to-generate-quarterly-report-and-sum-by-each-quarter-in-a-year-in-mysql

1条答案

按热度按时间

dy1byipe1#

这可以使用条件聚合重写，并且应该从终端驱动，因为假设加载详细信息包含所有终端是不安全的。

SELECT t.name as terminals, 
    sum(case when bl.month in ('January','February','march') then bl.barrels_net_qty else 0 end) as 'IST QTR', 
    sum(case when bl.month in ('April','May','June') then bl.barrels_net_qty else 0 end) as '2ND QTR', 
    sum(case when bl.month in ('July','AUgust','September') then bl.barrels_net_qty else 0 end) as '3RD QTR', 
    sum(case when bl.month in ('October','November','December') then bl.barrels_net_qty else 0 end) as '4TH QTR', 
    sum(coalesce(bl.barrels_net_qty,0))  as total 
FROM terminals as t 
left loadingdetails  as l on t.id = l.terminal_id
LEFT JOIN bldetails as bl on bl.operation_no = l.operation_no AND l.year = '2020' 
GROUP BY t.name ;

赞(0）回复(0）举报 2021-07-27

我来回答

如何在mysql中生成季度报表，并按季度求和

1条答案

相关问题

热门标签

最新问答