选择总和
问题描述:
我有两个表“发票”和“invoices_account”选择总和
通过查询
SELECT DATE_FORMAT(inv_created, '%Y') as 'year',
COUNT(*) as 'total'
FROM invoices as i
WHERE inv_way = "OUT"
GROUP BY DATE_FORMAT(inv_created, '%Y')
我拿到发票的金额,每年这样的:
2011 | 80
2012 | 120
等。
我需要的是在第一个查询中,按年份从“invoices_account”。“inv_total”中获取所有条目的摘要。关系是1:1。这意味着2011年的80份发票在“invoices_account”中有80行。我需要有这样的事情
2011 | 80 | 1.000.000 €
2012 | 120 | 230.000.000 €
答
SELECT DATE_FORMAT(inv_created, '%Y') as 'year',
COUNT(*) as 'total',
SUM(ia.inv_total) as money_total
FROM invoices as i
JOIN invoices_account ia
ON i.id = ia.id
WHERE inv_way = "OUT"
GROUP BY DATE_FORMAT(inv_created, '%Y')