内连接中三列的总和

I am trying to link two tables together so I can get the following data out of them. But all I get is all the totals added to the first person and nothing for and other people. It should look like it does below

我试图将两个表链接在一起，以便我可以从中获取以下数据。但我得到的只是第一个人的所有总数，而没有给其他人。它看起来应该如下所示

pid firstname nid  Total
18  Teresa     3  65846.50
18  Richard    1  3005.00 
18  Camdon     7  2000.00

The join works but the sum is not working correctly

连接有效，但总和无法正常工作

SELECT
    notarys.pid,
    notarys.firstname,
    notarys.nid,
    SUM(
    signings.samount+
    signings.prtamount+
    signings.faxamount
    )AS Total
    FROM
        notarys
    INNER JOIN signings ON notarys.nid = signings.nid
    WHERE
        signings.pid = signings.pid
    AND signings.done = 1
    ORDER BY
    notarys.nid ASC

1 个解决方案

#1

A GROUP BY is needed.

需要GROUP BY。

SELECT
    notarys.pid,
    notarys.firstname,
    notarys.nid,
    SUM(
    signings.samount+
    signings.prtamount+
    signings.faxamount
    )AS Total
FROM
    notarys
INNER JOIN signings ON notarys.nid = signings.nid
WHERE
    signings.pid = signings.pid
AND signings.done = 1
GROUP BY notarys.nid, notarys.pid
ORDER BY notarys.nid ASC