Error adding values of a select in mysql

0

My code in MYSQL is:

SELECT COALESCE(payments.base,0) as total, count(*) 
FROM 'payments' 
INNER JOIN 'pagos' ON 'pagos'.id = 'payments'.pago_id 
INNER JOIN 'documents' ON 'documents'.id = 'pagos'.document_id 
INNER JOIN 'clients' ON 'clients'.id = 'documentos'.client_id 
WHERE (pagos.money=1 AND (clients.group_id = 14) AND (payments.deleted = 0)
GROUP BY payments.base
ORDER BY payments.base

The result I have is:

total  | count(*)
54.8   |  3
399.76 |  2

What I am needing is that the group of 54.8 + 399.76 be added to total = 454.56 .

What do I correct to obtain the expected result?

    
asked by DAES 04.01.2017 в 02:45
source

1 answer

0

The expected result is not clear to me. I leave you an option, and it will be your turn to clarify if it is not what you want to say.

If you want the query to return a single record with the total of the groups 54.8 and 399.76 , you can achieve it by eliminating the GROUP BY (and the ORDER BY ) and using a SUM(DISTINCT ...) :

SELECT SUM(DISTINCT COALESCE(payments.base,0)) as total
FROM 'payments' 
INNER JOIN 'pagos' ON 'pagos'.id = 'payments'.pago_id 
INNER JOIN 'documents' ON 'documents'.id = 'pagos'.document_id 
INNER JOIN 'clients' ON 'clients'.id = 'documentos'.client_id 
WHERE (pagos.money=1 AND (clients.group_id = 14) AND (payments.deleted = 0)
    
answered by 04.01.2017 / 03:00
source