说我有两个表People
和Invoices
。
人
firstname postcode
Alex 2403
Peter 2357
Michael 3456
发票
person earned
Alex 12300
Alex 3556
Peter 1000
Alex 234
Michael 10000
我想向称为收入的人添加一列,显示每个人的总收入 - 我该如何实施?
我相信应该加入表格:
INNER JOIN Invoices ON People.firstname = Invoices.person
,但是我不确定如何将收入的钱归纳为相应的收入(我假设COUNT(*)
在某个地方使用)。
显然依靠'firstName'和'person'作为加入,因为名称不是唯一的,但是假设您使用了任意数据示例:
select p.firstname, p.postcode, sum(i.earned) earnings from people p
inner join invoices i on p.firstname = i.person
group by i.person
给出
| *firstname* | *postcode* | *earnings* |
+-------------+------------+------------+
| Alex | 2403 | 16090 |
| Michael | 3456 | 10000 |
| Peter | 2357 | 1000 |
也许不是最受人尊敬的资源,但是此链接应有助于更多地了解与GROUP BY
一起使用CC_4
https://www.w3resource.com/sql/aggregate-functions/sum-with-group-by.php
,因为我无法弄清楚您的表格,我认为此代码会有所帮助。
SELECT
a.firstname,
a.postcode,
SUM(b.earned)
FROM
People a
LEFT OUTER JOIN
Invoices b
ON
a.firstname = b.person
GROUP BY
a.firstname
如果您可以添加有关表格的更多详细信息,那就太好了。
select p.firstname,sum(p.income)+sum(i.earned) from people p inner join
invoice i on p.firstname = i.firstname where p.firstname =
'Alex' group by p.firstname;
此查询必须解决您的问题
对不起, SUM
应该是。
SELECT
firstname,
SUM(earned) AS earnings
FROM People
INNER JOIN Invoices
ON People.firstname = Invoices.person
GROUP BY person
SELECT
firstname,
count(earned) AS earnings
FROM People
INNER JOIN Invoices
ON People.firstname = Invoices.person
GROUP BY person