Sum results values in mysql

Question

I am servicing a bank and am finding it difficult to make a select.

Table structure descontos_taxas:
      id | value | client | data_created

My Scenario: This table holds both rates and discounts in the same table. Rates are positive values and discounts are negative values.
I need to take all the values of all customers, however, if a customer has received a fee and a discount on the same day I need to get only one result with the difference between the two.

So far I’ve done this:

SELECT 
     *
FROM 
    descontos_taxas as dt
WHERE 
    YEAR(dt.data_criado) = 2014 and MONTH(dt.data_criado) = 04 and dt.valor < 0
GROUP BY 
    dt.data_criado, dt.cliente
ORDER BY 
    data_criado asc;

How to return only negative amounts of the result of the sum of fees and discounts applied to the same customer? (Customers who do not have fees applied on the same day of the discount the amount would be added to 0).

I have this result in select:

 valor |    cliente    | data_criado
-19,90 | Erlon Charles | 2014-04-01
  9,90 | Erlon Charles | 2014-04-01
-19,90 | Erlon Charles | 2014-04-05
-19,90 | Erlon Charles | 2014-04-19

I need this result to match this:

 valor |    cliente    | data_criado
-10,00 | Erlon Charles | 2014-04-01 //aqui estão os dados das tuplas 1 e 2 somados
-19,90 | Erlon Charles | 2014-04-05
-19,90 | Erlon Charles | 2014-04-19

Answer 1

I added having to filter after group, and separation of rates and discounts:

SELECT
    SUM( IF( valor > 0, valor, 0 ) ) AS taxas,
    SUM( IF( valor < 0, -valor, 0 ) ) AS descontos,
    SUM(valor) AS total,
    cliente,
    data_criado
FROM
    descontos_taxas
WHERE
    YEAR(data_criado) = 2014 and MONTH(data_criado) = 04
GROUP BY
    cliente, data_criado
ORDER BY
    data_criado asc
HAVING
    total < 0;

Answer 2

If I understood correctly it would be the Sum of Fees, the Sums of Discounts and then the Difference of the two, grouped by customer and data_created:

SELECT a.cliente, 
       a.data_criado, 
       a.taxas, 
       a.desconto,  
       (a.taxas - abs(a.desconto)) diferenca
FROM (
SELECT cliente, data_criado, 
    sum(CASE WHEN valor > 0 THEN VALOR ELSE 0 END) taxas,
    sum(CASE WHEN valor < 0 THEN VALOR ELSE 0 END) desconto
FROM descontos_taxas
GROUP BY cliente, data_criado) as a

If you still need to filter:

SELECT a.cliente, 
       a.data_criado, 
       a.taxas, 
       a.desconto,  
       (a.taxas - abs(a.desconto)) diferenca
FROM (
SELECT cliente, data_criado, 
    sum(CASE WHEN valor > 0 THEN VALOR ELSE 0 END) taxas,
    sum(CASE WHEN valor < 0 THEN VALOR ELSE 0 END) desconto
FROM descontos_taxas
GROUP BY cliente, data_criado) as a
WHERE date_format(a.data_criado, '%Y-%m') = '2014-04'

Upon request the reply

SELECT a.cliente, 
       a.data_criado, 
       (a.taxas - abs(a.desconto)) valor
FROM (
SELECT cliente, data_criado, 
    sum(CASE WHEN valor > 0 THEN VALOR ELSE 0 END) taxas,
    sum(CASE WHEN valor < 0 THEN VALOR ELSE 0 END) desconto
FROM descontos_taxas
GROUP BY cliente, data_criado) as a

Answer 3

You need to group the result by customer and by day, and need to remove the condition dt.valor < 0 query. It needs to enter as a having (which is processed after operations). See:

SELECT 
     dt.cliente, dt.data_criado, sum(valor) AS resultado
FROM 
    descontos_taxas as dt
WHERE 
    YEAR(dt.data_criacao) = 2014 and MONTH(dt.data_criacao) = 04 
GROUP BY 
    dt.cliente, DATE_FORMAT(data_criado, "%d/%m/%Y")
ORDER BY 
    data_criado asc
HAVING
    resultado < 0;

I didn’t test the query, it’s just a direction.