Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

sql - MySQL Multiple Counts in Single Query

I am trying to COUNT two columns in a single query, but the results are spitting 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;
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Use:

   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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...