Summing multiple columns - unexpected results

Question

Why

Does this give incorrect results?

SELECT
    people.name,
    SUM(allorders.TOTAL),
    SUM(allorders.DISCOUNT),
    SUM(allorders.SERVICECHARGE),
    SUM(payments.AMOUNT)
FROM
    people
INNER JOIN
    allorders ON allorders.CUSTOMER = people.ID
INNER JOIN
    payments ON payments.CUSTOMER = people.ID
WHERE
    people.ID = 7 AND allorders.VOIDED = 0 AND payments.VOIDED = 0

Gives: (the name), 1644000, 1100000, 50000, 1485000

If I do it two tables at a time (INNER JOIN people ON allorders.CUSTOMER = people.ID) in separate queries, I get the correct results. I don't don't even know where the numbers I get come from. Like:

SELECT
    people.name,
    SUM(allorders.TOTAL),
    SUM(allorders.DISCOUNT),
    SUM(allorders.SERVICECHARGE)
FROM
    people
INNER JOIN
    allorders ON allorders.CUSTOMER = people.ID
WHERE people.ID = 7 AND allorders.VOIDED = 0

Gives: (the name), 822000, 550000, 25000

SELECT
    people.name,
    SUM(payments.AMOUNT)
FROM
    people
INNER JOIN payments ON payments.CUSTOMER = people.ID
WHERE people.ID = 7 AND payments.VOIDED = 0

Gives: (the name), 297000

It looks like it doubles, but I don't know why.

The odd thing is I have a similar query that does this sum correctly. I'll post it, but it's a bit complex. Here goes:

SELECT
    t1.IDENTIFIER,
    ifnull(t1.NAME,""),
    t1.PRICE,
    t1.GUESTS,
    t1.STATUS,
    ifnull(t1.NOTE,""),
    t1.LINK,
    ifnull(t1.EDITOR,""),
    concat(t2.FIRSTNAME,"",t2.LASTNAME),
    t2.ID,
    t3.ID,
    ifnull(t1.EMAIL,""),
    ifnull(t3.PHONE,""),
    ifnull(SUM(p1.AMOUNT),0),
    ifnull(SUM(o1.DISCOUNT),0),
    ifnull(SUM(o1.TOTAL),0),
    ifnull(SUM(o1.SERVICECHARGE),0)
FROM
    tables t1
INNER JOIN
    people t2 ON t1.SELLER = t2.ID
INNER JOIN
    people t3 ON t1.CUSTOMER = t3.ID
INNER JOIN
    orderpaymentinfo ON orderpaymentinfo.TABLEID = t1.IDENTIFIER
INNER JOIN
    payments p1 ON orderpaymentinfo.PAYMENTID = p1.PAYMENTID
INNER JOIN
     allorders o1 ON o1.ORDERID = orderpaymentinfo.ORDERID
WHERE
    p1.VOIDED = 0 AND o1.VOIDED = 0 AND t1.DATE = "2014-12-20"
GROUP BY t1.IDENTIFIER

The latter statement does the same join, only it uses an additional helper-table. I'm sorry it's a bit poorly formatted (I'm not great with SO's formatter), but if someone can tell me the difference between the logic in these two statements and how one can be completely wrong while the other right, I'd be very happy.

In response to answer:

Result 1:

Name - 5

Result 2:

Name - 2

Result 3:

Name - 10

Result 4 is truncated in phpMyAdmin - where would I get this easily?

Table structure for the three tables looks like:

SHOW create on the way.

Answer 1

Okay, so I am pretty sure you've a join condition that's basically exploding your result set into something like a Cartesian product. Here's what I think you should try

First, run the following and share the output:

SELECT p.name,COUNT(*) 
FROM people  as p
INNER JOIN allorders  AS a
ON a.CUSTOMER = p.ID 
WHERE p.ID = 7 AND a.VOIDED = 0
GROUP BY p.name

Then run

SELECT p.name,COUNT(*) 
FROM people  AS p
INNER JOIN payments AS pay
ON pay.CUSTOMER = p.ID 
WHERE p.ID = 7 AND pay.VOIDED = 0 
GROUP BY p.name

Then run

 SELECT
        p.name,
        COUNT(*) 
   FROM
        people as p
    INNER JOIN
        allorders as a ON a.CUSTOMER = p.ID
    INNER JOIN
        payments as pay ON pay.CUSTOMER = p.ID
    WHERE
        p.ID = 7 AND a.VOIDED = 0 AND pay.VOIDED = 0
   GROUP BY p.name

Last run the following

SHOW CREATE TABLE people;
SHOW CREATE TABLE payments;
SHOW CREATE TABLE allorders;

The problem is that you don't have the correct understanding of your data. You need to give us a bit more info about the data and the relationships, and the output I've described here should help. Mine is not an answer. But if you run these queries and paste the output of them, you should be able to get an answer, either from me or someone else.

Based on the discussion and edits above, please try:

SELECT
    p.name,
    SUM(o.TOTAL),
    SUM(o.DISCOUNT),
    SUM(o.SERVICECHARGE),
    MAX(pay.amt) 
FROM
    people as p
INNER JOIN
    allorders AS o ON o.CUSTOMER = p.ID
INNER JOIN (SELECT customer, 
            SUM(amount) as amt 
            FROM payments 
            WHERE voided = 0 AND customer = 7 
            GROUP BY customer) AS pay
     ON pay.customer = p.id
WHERE
    p.ID = 7 AND o.VOIDED = 0 
GROUP BY p.name

You could also do a subquery in your SELECT statement, but it's pretty obnoxious imo. You could also do min(pay.amt) or avg or even just leave the aggregate out altogether. The above should work... even though there are cleaner ways. I'm providing this answer so you can reason about why you were getting the unexpected result... actually optimizing your query is a different question that you can dive into later, once you've had a chance to look over this