Need help on MYSQL QUERY (SUM)

Vote count:

0

I need some help from you with a SELECT QUERY. I just wanted to sum correctly the following table. The table is a synthese from several other tables. I probably will add an id int value for student's name instead of text to make it easier to goup - but this is not my problem.

I want to sum for each student the three types of absent lessons. But with my sql I got only one each time! What am I doing wrong? I tried several changes but I didn't find it out..

I wanted to add a screen from my mysql table but I don't have enough reputation..

SELECT DISTINCT (P.synthese_abs_schueler_name) AS synthese_abs_schueler_name,
    SUM(A.synthese_sum) AS absent,
    SUM(B.synthese_sum) AS nonexcused,
    SUM(C.synthese_sum) AS tardive,
SUM(absent + nonexcused) AS totalabs,
FROM 
synthese_abs AS P
LEFT JOIN (SELECT synthese_abs_schueler_name, synthese_abs_type, synthese_abs_sum AS synthese_sum FROM synthese_abs WHERE synthese_abs_type = 1 AND synthese_abs_sum >0  GROUP BY synthese_abs_schueler_name) AS A
ON A.synthese_abs_type = P.synthese_abs_type AND A.synthese_abs_schueler_name = P.synthese_abs_schueler_name

LEFT JOIN (SELECT synthese_abs_schueler_name, synthese_abs_type, synthese_abs_sum AS synthese_sum FROM synthese_abs WHERE synthese_abs_type = 2 AND synthese_abs_sum >0 GROUP BY synthese_abs_schueler_name) AS B
ON B.synthese_abs_type = P.synthese_abs_type AND B.synthese_abs_schueler_name = P.synthese_abs_schueler_name

LEFT JOIN (SELECT synthese_abs_schueler_name, synthese_abs_type, synthese_abs_sum AS synthese_sum FROM synthese_abs WHERE synthese_abs_type = 3 AND synthese_abs_sum >0 GROUP BY synthese_abs_schueler_name) AS C
ON C.synthese_abs_type = P.synthese_abs_type AND C.synthese_abs_schueler_name = P.synthese_abs_schueler_name

ORDER BY P.synthese_abs_schueler_name

asked 36 secs ago

Ameliore

1