我试图写出每月销售额和利润的细分,并且合并功能不能按预期工作,即不会按我的意愿改变NULL .
这是我的代码:
SELECT coalesce (extract(month from o.order_date),'Total year') AS mois,
sum(op.selling_price * op.quantity) AS 'Monthly sales',
sum(op.selling_price * op.quantity) - sum(p.buying_price * op.quantity) AS 'Monthly Profit',
count(r.return_id)
FROM order_products op
JOIN orders o
ON o.order_id = op.order_id
LEFT JOIN returns r
ON r.order_product_id = op.order_product_id
JOIN products p
ON p.product_id = op.product_id
WHERE extract(year from o.order_date) = '2016'
GROUP BY mois
WITH ROLLUP;
运行此代码时,最后一行(汇总)仍然在“mois”列下显示“NULL” .
关于我可能错过的任何想法?
我已尝试使用函数ifnull但我遇到了同样的问题 .
谢谢!
1 回答
将查询使用汇总放在子查询中,并在主查询中使用
COALESCE
.