Search code examples
mysqlgroup-byinner-join

join on a calculated column value fails to give the expected result

table movimenti

id data data_val descrizione banca movimento importo id_volte saldo_carta utenza created_on
3 2021-03-14 2021-03-14 Rata Mutuo 6 21 560.00 5 NULL 2 2021-03-02 17:57:38
4 2021-04-14 2021-04-14 Rata Mutuo 6 21 560.00 5 NULL 2 2021-03-02 17:57:38
27 2021-03-01 2021-03-27 Stipendio Mensile 6 20 2500.00 6 NULL 2 2021-03-02 17:57:47
28 2021-04-01 2021-04-27 Stipendio Mensile 6 20 2500.00 6 NULL 2 2021-03-02 17:57:47
49 2021-03-02 2021-04-16 prova 6 21 134.00 0 3032021 2 2021-03-02 19:23:02
51 2021-03-05 2021-04-16 prova1 6 21 432.00 0 3032021 2 2021-03-02 19:48:35

table movimenti_periodici

id nome
0 Singolo
5 mutuo
6 stipendio

Table causali

id nome
23 Interessi Attivi
24 Interessi Passivi
21 Rata Mutuo
13 Saldo carta di credito
20 Stipendio

Query:

SELECT 
    MAX(data_val) as data, 
    IF(COUNT(*) > 1, 'Saldo Carta', MAX(descrizione)) AS descrizione,
    IF(COUNT(*) > 1, 13, MAX(movimento)) AS movimento,
    MAX(causali.nome),
    SUM(importo) as importo, 
    MAX(movimenti_periodici.nome) as periodico
FROM `movimenti` 
    JOIN causali ON movimenti.movimento=causali.id 
    JOIN movimenti_periodici ON movimenti.id_volte=movimenti_periodici.id 
WHERE movimenti.utenza=2 
    AND data BETWEEN '2021-03-01'  AND '2021-04-30' 
    AND movimenti.banca=6
 group by saldo_carta, data_val
 ORDER BY data_val ASC

Expected result

data descrizione movimento nome importo periodico
2021-03-14 Rata Mutuo 21 Rata Mutuo 560.00 mutuo
2021-03-27 Stipendio Mensile 20 Stipendio 2500.00 stipendio
2021-04-14 Rata Mutuo 21 Rata Mutuo 560.00 mutuo
2021-04-16 Saldo Carta 13 Saldo Carta di credito 566.00 Singolo
2021-04-27 Stipendio Mensile 20 Stipendio 2500.00 stipendio

My query works fine and aggregates together rows 49 and 51 of table movimenti (see expected results and actual results for 2021-04-16 row). For this aggregated movement movimento is correctly set to 13 but I cannot use 13 in the join with causali to show the nome matching 13. It is currently returning the MAX(nome) from the rows that have been aggregated. The actual result looks like this:

Actual result

data descrizione movimento nome importo periodico
2021-03-14 Rata Mutuo 21 Rata Mutuo 560.00 mutuo
2021-03-27 Stipendio Mensile 20 Stipendio 2500.00 stipendio
2021-04-14 Rata Mutuo 21 Rata Mutuo 560.00 mutuo
2021-04-16 Saldo Carta 13 Rata Mutuo 566.00 Singolo
2021-04-27 Stipendio Mensile 20 Stipendio 2500.00 stipendio

How can I get my expected result?

Solution

  • I think that the easiest way to do it is to replace:

    MAX(causali.nome)

    with:

    IF(COUNT(*) > 1, (SELECT nome FROM causali WHERE id = 13), MAX(causali.nome))

    because as I see in your code, 13 is a hardcoded value.

    See the demo (where I also used aliases for all the tables to shorten the code and make it more readable).