mysql - get total submissions and number of accepted and rejected submissions of all users -

i have 2 tables in database, have fetch list. first table users table ordinary table information of users. table submissions table have list of submissions of user. instance can assume table follows

id user_id submission_name status 1   1         nnnn          pending 2   1         fgs           accepted 3   2         sds           rejected 4   2        asdasd         pending 

i want data follows (assuming left join of users , submissions table)

id username   total_submissions accepted_subs rejected_subs pending_subs 1   abc           2                    1            0         1 2   xyz           2                    0            1         1 

i using mysql. query can used data this? able data

id username   total_submissions      1   abc           2            2   xyz           2       

obviously, given data have provided, cannot username. rest of question solved using conditional aggregation:

select s.user_id, count(*) total_submissions,        sum( s.status = 'accepted' ) accepted,        . . .  -- add additional expressions here other statuses submissions s group s.user_id; 

you should able how join tables user name.