I have a problem with this mysql query. basically I need to join one table ( CostiFissi , table with costs) with a table with payments ( Pagamenti ), grouping them by cost identifier (CostiFissi_IdCostoFisso) and calculating the average between monthly payments (not only AVG (Totale)).
that is: the average value between 1000 + 2000 in September and 3000 in October should return 2250, not 3000
this is what i have done so far:
SELECT `cf`.`IdCostoFisso`, `cf`.`Nome`, `cf`.`Frequenza`, `cf`.`Importo`, `cf`.`DateFrom`, `cf`.`DateTo`, SUM( p.Totale ) PagamentiTotale, COUNT( p.IdPagamento ) PagamentiNum, AVG(p2.somma_mese) Media FROM (`CostiFissi` cf) LEFT JOIN `Pagamenti` p ON `p`.`CostiFissi_IdCostoFisso` = `cf`.`IdCostoFisso` LEFT JOIN ( SELECT MONTH(Data), YEAR(Data), CostiFissi_IdCostoFisso, SUM(Totale) somma_mese FROM Pagamenti GROUP BY YEAR(Data), MONTH(Data), CostiFissi_IdCostoFisso ) AS p2 ON `p2`.`CostiFissi_IdCostoFisso` = `cf`.`IdCostoFisso` WHERE `cf`.`DateTo` > '2012-09-27 09:46:14' AND `p`.`Data` >= '2012-01-01 00:00:01' AND `p`.`Data` <= '2012-12-31 23:59:59' GROUP BY `cf`.`IdCostoFisso`
than when I run the request, I get this problem: let's say I have 2 costs, cost_a (w / 3 payments) and cost_b (w / 1 payment), I return the correct average for both (since I want this) but COUNT (p.IdPagamento) returns 6 (not 3) for cost_a and 1 for cost_b. same for SUM (p.Totale), which doubles for cost_a, not cost_b.
perhaps the problem with joining the table p, dunno ... it took me a while to get to this point, but now it's a bit messy and I can't go further> _ <
ty!
source share