在groupBy选择后计算单个用户的总记录数

Question

I have a mysql select query that has a groupBy. I want to count all the records after the group by statement for individual user. Is there a way for this directly from mysql ?

$query = mysql_query('SELECT distinct(tape_no) , user_id, username FROM `audio_history`  group by username , tape_no');
$i=1;
$temp = '';
 while($res = mysql_fetch_object($query)){
  if($temp != $res->username && $temp != ''){
  echo $res->user_id . '---' .$temp  . "$i<br>";
  $i = 0;
  }
 temp = $res->username;
 $count = $i;
 $i++;
 }

sql statement

SELECT distinct(tape_no) ,
 user_id, username 
FROM `audio_history`  
group by username , tape_no

returns

tape_no user_id username    
001 790 adam
422 790 adam
903 001 alic
585 005 cooper
356 005 cooper
697 005 cooper

I want the output to be returned as

 uname    uif  count_for_individual_user
 adam     790     2
 alic     001     1
 cooper   005     3

I need to get the simplest way of doing this without using php code , only by querying. Thanks in advance

Answer 1

To get a count of the DISTINCT values of tape_no for each user, that is, not including any "duplicate" values of tape_no in the count, add the DISTINCT keyword inside the COUNT aggregate.

SELECT username                AS uname
     , user_id                 AS uif 
     , COUNT(1)                AS count_for_individual_user
     , COUNT(DISTINCT tape_no) AS count_distinct_tape_no
  FROM `audio_history`
 GROUP BY username, user_id 

Given these rows:

username user_id tape_no
-------- ------- -------
adam     790     001
adam     790     422
adam     790     422

Then the query above will

return 3 for the COUNT(1) expression (because there are three rows for (adam,790), and will

return 2 for the COUNT(DISTINCT tape_no) expression, because there are only two distinct values of tape_no for (adam,790).