test

SELECT 
    User.id
    ,User.login
    ,User.password
    ,City.name
    ,School.name
    ,(CASE WHEN User.failed_transactions_count > 0 THEN
        1 
    ELSE
        0
    END) AS has_failed_transactions,
    Transaction.amount
FROM
    User 
    JOIN City ON User.city_id = City.id
    LEFT JOIN School ON User.school_id = School.id 
    LEFT JOIN (SELECT 
                    Transaction.user_id
                    ,SUM(Transaction.amount) AS amount
               FROM
                    Transaction
               GROUP BY
                    Transaction.user_id
              ) AS Transaction ON User.id = Transaction.user_id
WHERE
    User.gender = 'M'
    AND User.active = 1
    AND User.user_status_id = 1
ORDER BY
    User.login