Adding result values in mysql

Navigation
2

I'm servicing a bank and I'm having trouble making a select .

Structure of table descontos_taxas :
id | value | customer | data_created

My Scenario: This table stores both fees and discounts in the same table. The rates are positive values and the discount negative values.
I need to get all the values of all the clients, 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 the following: 

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 the negative values of the result of the sum between rates and discounts applied to the same customer? (Customers without taxes applied on the same day as the discount would add up 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 be equal to 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
    
asked by anonymous 14.05.2014 / 22:40

3 answers

4

I added having to filter after the group, and the separation of fees 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;
    
15.05.2014 / 04:31
2

If I understood correctly it would be the Sum of Rates, the Sums of Discounts and then the Difference of the two, grouped by client and date_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'

As requested the response 

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
    
15.05.2014 / 01:26
1

You need to group the result by the client and by day, and need to remove the dt.valor < 0 condition from the query. It needs to come in as a having (which is processed after the 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 did not test the query, it's just a targeting.

    
14.05.2014 / 23:06
Doubts about extends Application javascript compression techniques and tips