mysql中sum的总和
SELECT
f.nummer AS factuur_id,
f.mvoor AS factuur,
f.faktuurnr AS factuur_nr,
f.topdatum AS factuur_datum,
f.vervaldag AS factuur_vervaldatum,
f.totbruto AS factuur_totaal,
SUM(c.totbruto) AS credit_totaal,
SUM(b.bedrag) AS bedrag_totaal,
f.totbruto - (SUM(c.totbruto) + SUM(b.bedrag)) AS SOM
FROM ((facturen AS f LEFT JOIN creditnota AS c ON f.nummer = c.nummer)
LEFT JOIN betaling1 AS b ON f.nummer = b.factuurnr)
LEFT JOIN klanten AS k ON f.klantnr = k.nr
WHERE f.betaald = 'N'
AND CURDATE() >= DATE_ADD(f.vervaldag, INTERVAL 15 DAY)
GROUP BY f.nummer
HAVING (factuur_totaal - (SUM(c.totbruto) + SUM(b.bedrag))) > 0
ORDER BY k.naam
现在我想要 SOM 的 SUM 我需要子查询吗? 我该怎么做?
我知道我可以循环它,但我希望它在 1 个查询中。
mysql,php
谢谢!
SELECT
f.nummer AS factuur_id,
f.mvoor AS factuur,
f.faktuurnr AS factuur_nr,
f.topdatum AS factuur_datum,
f.vervaldag AS factuur_vervaldatum,
f.totbruto AS factuur_totaal,
SUM(c.totbruto) AS credit_totaal,
SUM(b.bedrag) AS bedrag_totaal,
f.totbruto - (SUM(c.totbruto) + SUM(b.bedrag)) AS SOM
FROM ((facturen AS f LEFT JOIN creditnota AS c ON f.nummer = c.nummer)
LEFT JOIN betaling1 AS b ON f.nummer = b.factuurnr)
LEFT JOIN klanten AS k ON f.klantnr = k.nr
WHERE f.betaald = 'N'
AND CURDATE() >= DATE_ADD(f.vervaldag, INTERVAL 15 DAY)
GROUP BY f.nummer
HAVING (factuur_totaal - (SUM(c.totbruto) + SUM(b.bedrag))) > 0
ORDER BY k.naam
And now I want a SUM from the SOM
Do I need a subquery for this?
And how to I do this?
I know I can just loop it but I want it in 1 query.
mysql, php
Thanks!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您可以这样做:
基本上,我刚刚将您的查询包装在
SELECT SUM() FROM () AS temp
中。You can do this:
Basically I've just wrapped your query in a
SELECT SUM(<some row>) FROM (<your query>) AS temp
.将
SOM
语句包装在另一个SUM()
调用中:Wrap the
SOM
statement in anotherSUM()
call: