Task: it is necessary to show how many boys and girls received this or that assessment.

A "clumsy" request was written for the task:

SELECT r.mark, COUNT(r.mark), (SELECT COUNT(r1.mark) FROM results r1 JOIN users u1 ON (r1.id_user = u1.id AND r1.ex_mode = 'tmOlympDiagnostic' AND u1.gender = 'Ж')) FROM results r JOIN users u ON (r.id_user = u.id AND r.ex_mode = 'tmOlympDiagnostic' AND u.gender = 'М') GROUP BY r.mark ORDER BY r.mark desc;

Conclusion:

  Mark | BoysMarksCount | GirlsMarksCount ---------+----------------+---------------- 5 | 8 | 2 4 | 1 | 2 3 | 1 | 2 2 | 2 | 2 1 | 1 | 2 ---------+----------------+----------------

For boys, it was not difficult to do statistics, but for girls I don’t know how to issue a request correctly, so that the third column shows how many of each assessment girls received, because now the third column is not grouped by the "Mark" field and as a result, having diagnostic results only from two girls, in each column displays 2 pcs. for each evaluation.

    1 answer 1

     SELECT r.mark mark , COUNT(r.mark) total , SUM(CASE WHEN u.gender = 'М' THEN 1 ELSE 0 END) male , SUM(CASE WHEN u.gender = 'Ж' THEN 1 ELSE 0 END) female FROM results r JOIN users u ON r.id_user = u.id WHERE r.ex_mode = 'tmOlympDiagnostic' GROUP BY r.mark ORDER BY r.mark DESC;
    • Guru, thank you very much! - user214690
    • I do not quite understand the expression SUM(CASE WHEN u.gender = 'М' THEN 1 ELSE 0 END) . Is it right that when after grouping across the r.marks field (here it brought all assessments with duplicates), the SUM function, passing through the grouped issuance of ratings, adds 1 if the floor matches, and 0 if not? - user214690
    • Well, if very rude, then yes. - Akina
    • Thanks again! - user214690
    • Do not tell me how to make a percentage in the same request (how many in total, how many for boys and how many for girls)? - user214690