MySQL Forums
Forum List  »  Newbie

Sum from two different tables
Posted by: Andi Oliver Ion
Date: July 04, 2005 12:46PM

select clienti.idc as thisindex, concat(clienti.nume, ' ', clienti.prenume) as 'Denumire client',
sum(facturare.total) as 'nomTotal facturat',
sum(chitante.val) as 'nomTotal achitat',
from((((clienti inner join abonament on clienti.idc=abonament.idc)
inner join abonamente on abonament.ida=abonamente.ida)
left outer join facturare on abonament.idas=facturare.idas)
left outer join chitante on facturare.idfs=chitante.idfs)
where (clienti.realizat='da' and clienti.pierdut='nu' and clienti.special='nu') and (tip='p') and clienti.nume='Saracila'
group by clienti.idc order by clienti.nume, clienti.prenume

The problem in this query is that want to retrieve sum from two columns which resides in separate tables. 'chitante.val' has stored two records which must calculate the sum, and 'facturare.total' - in my case - has only one.
My problem is that instead to return a single value of facturare.total, it make it double regarding the fact that chitante.val has two records.
If rec1 of chitante.val is 10
rec2 of chitante.val is 5
rec 1 and single on of facturare.total is 15
it should return me:
sum(facturare.total)=15
sum(chitante.val) = 15
Instead of this calculation it returns me:
sum(facturare.total) = 30
sum(chitante.val) = 15
Any idea how I am supposed to tell the query to load the value from facturare as it is: just 15 (just single time)?

Options: ReplyQuote


Subject
Written By
Posted
Sum from two different tables
July 04, 2005 12:46PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.