CODEWITHSUNDEEP
mysqlsql
YvetteLee
YvetteLee

Reputation: 1090

mySQL sum totals from two tables

I am trying to sum the PRICE from two tables which are connected by another table.

tblcustomer   tblappointment   tblfinances
----------    --------------   -----------
CustomerID    AppointmentID    FinancesID
Name          CustomerID       CustomerID
              Price            Price

I am trying this code but I am getting an error #1064

SELECT c.CustomerID, (1.p1+2.p2) AS total_price FROM tblcustomer c 
LEFT JOIN (SELECT CustomerID, SUM(Price) p1 FROM tblappointment GROUP BY CustomerID) 1 ON 1.CustomerID = c.CustomerID
LEFT JOIN (SELECT CustomerID, SUM(Price) p2 FROM tblfinances GROUP BY CustomerID) 2 ON 2.CustomerID = c.CustomerID;

Upvotes: 1

Views: 50

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271013

I do not recommend using aliases that are or start with numbers. More importantly, you need to recognize that the JOINs may not match -- that would be why you are using outer joins in the first place.

So:

SELECT c.CustomerID,
       (COALESCE(a.price, 0) + COALESCE(f.price, 0)) AS total_price
FROM tblcustomer c LEFT JOIN
     (SELECT CustomerID, SUM(Price) as price
      FROM tblappointment
      GROUP BY CustomerID
     ) a
     ON a.CustomerID = c.CustomerID LEFT JOIN
     (SELECT CustomerID, SUM(Price) as price
      FROM tblfinances
      GROUP BY CustomerID
     ) f
     ON f.CustomerID = c.CustomerID;

Upvotes: 2

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

You need to fix aliasing as follows

SELECT c.CustomerID, (t1.p1+t2.p2) AS total_price 
  FROM tblcustomer c 
  LEFT JOIN (SELECT CustomerID, SUM(Price) as p1 
               FROM tblappointment 
              GROUP BY CustomerID) t1 ON t1.CustomerID = c.CustomerID
  LEFT JOIN (SELECT CustomerID, SUM(Price) as p2 
               FROM tblfinances 
              GROUP BY CustomerID) t2 ON t2.CustomerID = c.CustomerID;

since aliases can not be totally numeric expressions except they're quoted

Upvotes: 2

Related Questions