Mysql – SELECT: math and comparing mulitple tables

MySQL

I have two tables that are similar in nature except for the increase in values over time. I need to pull data and manipulate with math functions.

Example 1 earlier time

Name           Points
person1        500
person1        200
person2        300
person3        200

some have duplicate name entries with different point values

Example 2 – later time

Name           Points
person1        600
person1        205
person2        350
person3        200
person4        45

new person added later

the final result should calculate

Name       Combined Difference (laterearlier)  Weighted Total
person1    805700=105                          105X3000/200=1575
person2    350300=50                           50X3000/200=750
person3    200200=0                            0X3000/200=0
person4    45null=45                           45X3000/200=675

3000 is a constant
200 is combined total of differences

This works for 2 columns on 1 table

SELECT address, SUM(totalpts) AS DAY2
FROM table1
GROUP BY address
ORDER BY address

However when I try a JOIN it does not calculate properly

SELECT a1.address, SUM(a1.totalpts), a2.totalpts
FROM table1 AS a1
   LEFT JOIN table2 AS a2 ON a1.address = a2.address
GROUP BY a1.address
ORDER BY a1.address

I am comparing two specific dates or points in time. each date represents its own table – so there are two tables, identical structure, different data

Best Answer

JOIN creates more rows, then SUM adds up the extra rows.

Maybe this gives you what you want:

SELECT  a1.address,
        SUM(a1.totalpts),
        ( SELECT totalpts FROM table2 WHERE address = a1.address ) AS at_pts
    FROM table1 AS a1
    GROUP BY a1.address
    ORDER BY a1.address