Multiple MySQL counts in one query

Question

I try to COUNT two columns in the same query, but the results spit out the same values ​​for medcount and uploadcount. Any suggestions?

SELECT * , COUNT($tbl_list.listname) AS listcount, COUNT($tbl_uploads.id) AS uploadcount FROM $tbl_members LEFT JOIN $tbl_list ON $tbl_members.username = $tbl_list.username LEFT JOIN $tbl_uploads ON $tbl_members.username = $tbl_uploads.username GROUP BY $tbl_members.username ORDER BY $tbl_members.lastname, $tbl_members.firstname; 

Answer 1

Using:

  SELECT tm.*, x.listcount, y.uploadcount FROM $tbl_members tm LEFT JOIN (SELECT tl.username, COUNT(tl.listname) AS listcount FROM $tbl_list tl GROUP BY tl.username) x ON x.username = tm.username LEFT JOIN (SELECT tu.username, COUNT(tu.id) AS uploadcount FROM $tbl_uploads tu GROUP BY tu.username) y ON y.username = tm.username GROUP BY tm.username ORDER BY tm.lastname, tm.firstname