在MYSQL中，我希望获得具有多个条件的多个SUM来完成总计并获得价格里程碑

Question

Here is my SQL code. I have selected the price of each row and the milestone of each row and the id to join the tables, but I have only the result of one row.

PHP code to do multiple filters:

if($i==0)
{
    if($fc == "projet")
    {
        $filtre_bdd .= "AND p.$fc = '$fv' ";
    }
    else 
    {
        $filtre_bdd .= "AND cible.$fc = '$fv' ";
        $i= $i + 2;
    }
}

SQL query:

SELECT cible.prix,a.valeur,a.idroute,cible.id,p.id,
    SUM(DISTINCT a.valeur) AS total_avances, /* the milestone sum the unique one */
    SUM(a.valeur) AS total_avance, /* the milestone sum of each row */
    SUM(cible.prix) AS total_prix /* the price sum */
FROM avances AS a,routes AS cible,projets AS p
WHERE a.idroute = cible.id AND p.id = cible.idprojet $filtre_bdd /* the conditions to join the tables and do the filter */
GROUP BY cible.id, a.idroute 
HAVING total_avances <> cible.prix AND cible.prix - total_avances >- 1 

Now how can I get the totals of the prices and the total of the milestones with all those conditions? The targeted table contains the id of the project and the id of the milestones. We have to do the sum of the milestones for each row and sum of the sums and compare it to the project price.

Answer 1

i found the solution thank you

SELECT SUM(alucard.uu) as total_prix,SUM(alucard.vv) as total_avances FROM(
                                SELECT ultimate.id,SUM(ultimate.prix) as uu,SUM(ultimate.valeur) as vv FROM(
                                    SELECT e.id,e.prix,e.idprojet,e.valeur FROM(
                                        SELECT cible.id,cible.prix,cible.idprojet,a.valeur=4 AS valeur FROM avances AS a
                                            INNER JOIN routes cible ON a.idroute=cible.id 
                                            INNER JOIN projets p ON p.id=cible.idprojet $filtre_bdd 
                                             $date_bdd
                                            GROUP BY cible.id) AS e
                                    UNION 

                                    SELECT cible.id,cible.prix=4,cible.idprojet,SUM(a.valeur) FROM avances as a
                                        INNER JOIN routes cible ON a.idroute=cible.id 
                                        INNER JOIN projets p ON p.id=cible.idprojet $filtre_bdd 
                                        $date_bdd
                                        GROUP BY cible.id) AS ultimate
                                    GROUP BY ultimate.id
                                    HAVING SUM(ultimate.prix)>SUM(ultimate.valeur) ) AS alucard