MySQL Help Request

Question

Ok, I have a MYSQL query code setup to pull this information from my database. What I would like to do now is counted by code, but groups it by name.

$sql = "SELECT  m.* 
          FROM (SELECT DISTINCT Code  
                  FROM Transaction) md 
          JOIN Transaction m ON m.ID_Transaction = (SELECT  ID_Transaction
                                                      FROM Transaction mi
                                                     WHERE mi.Code = md.Code 
                                                       AND Date_Time=CURdate() 
                                                       AND Time_Stamp!=''
                                                  ORDER BY m.Name DESC, mi.Code DESC, mi.Date_Time DESC, mi.ID_Transaction DESC
                                                     LIMIT 1)";

With my php code, this is what I get from this request.

rick, 002455, 2010-11-10, 08:30 AM  
rick, 003819, 2010-11-10, 08:45 AM  
amber, 003572, 2010-11-10, 08:45 AM  
eric, 001479, 2010-11-10, 10:30 AM  
jerry, 001012, 2010-11-10, 09:45 AM   

Rick - name and name 000000 - code

I want to take this information and take it that way for every name.

rick = 2  
amber = 1  
eric = 1  
jerry = 1  

I'm not sure how I will do this? Can I use the information in the request to make another request to calculate this information.

Answer 1

SELECT m.name, count(*) FROM (
SELECT DISTINCT Code
FROM Transaction
) md JOIN Transaction m ON
m.ID_Transaction = ( SELECT ID_Transaction FROM Transaction mi WHERE mi.Code = md.Code AND Date_Time=CURdate() AND Time_Stamp!='' ORDER BY m.Name DESC, mi.Code DESC, mi.Date_Time DESC, mi.ID_Transaction DESC LIMIT 1 )
 group by m.name