CODEWITHSUNDEEP
mysqlgroup-by
Root
Root

Reputation: 2349

MySQL Group two column with where clause on both two group

What I have:
I have two table , first is user_faktorha save invoices data and second is u_payment save payment data .

What I want:
I want to group all data from this two table and have a result as one table with sum both table.
My two table with sample query's is on sqlfiddle : http://sqlfiddle.com/#!2/b9f9e/4

What's problem:
I try to solve this problem , but give wrong result each time , for example (can be see on sqlfiddle) , user/tell named as habib on give wrong sum(price) result.

habib's faktorhaprice = -508261 and habib's paymentprice = 648000 but sum result in main query have wrong data -7115654 and 13000000

what's the solution ?

Upvotes: 0

Views: 93

Answers (2)

user359040
user359040

Reputation:

(Updated) One way:

SELECT tell,SUM(FAKTORHAPRICE) FAKTORHAPRICE, SUM(PaymentPrice) PaymentPrice 
FROM (SELECT tell, price as FAKTORHAPRICE, null PaymentPrice
      from user_faktorha
      union all
      SELECT Username as tell, null as FAKTORHAPRICE, Price as PaymentPrice
      FROM `u_payment` WHERE Active='1') sq 
GROUP BY tell ORDER BY FAKTORHAPRICE ASC;

SQLFiddle here.

Upvotes: 1

Mike Brant
Mike Brant

Reputation: 71384

The essence of your problem here is that you are trying to relate to unrelated tables. Sure they have common data in the user name, but there is not a clean relation between them like an invoice id that can be used to relate the items together such that the OUTER JOIN wouldn't duplicate records in your result set. My suggestion would be to do the aggregation on each table individually and then join the results like this:

SELECT f.tell, f.faktorhaprice, p.paymentprice
FROM
    (SELECT tell, SUM(price) AS faktorhaprice FROM user_faktorha GROUP BY tell) AS f
INNER JOIN
    (SELECT username, SUM(price) AS paymentprice FROM u_payment GROUP BY username) AS p
ON f.tell = p.username

Upvotes: 0

Related Questions