Need query help for multiple columns

[Zergman]

I have the following code that works great for counting one column

<?php
$sql = "SELECT t2agent, COUNT(*) as total
          FROM data
          WHERE MONTH(`tdate`) = '$month'
          GROUP BY t2agent 
          ORDER BY total DESC";
$res = mysql_query($sql);
while (list($id, $tot) = mysql_fetch_row($res))
{
    echo "$id : $tot <br />";
}
?>

 

But I would like this to not just display the t2agent column when echoing.  There is about 3 other columns I would like this query to display. 

 

How would I modify this to pull more columns from the same table and display them in my echo while still counting just the t2agent column?

[ucffool]

<?php
$sql = "SELECT col1,col2,col3,t2agent,COUNT(t2agent) as total
          FROM data
          WHERE MONTH(`tdate`) = '$month'
          GROUP BY t2agent 
          ORDER BY total DESC";
?>

[fenway]

<?php
$sql = "SELECT col1,col2,col3,t2agent,COUNT(t2agent) as total
          FROM data
          WHERE MONTH(`tdate`) = '$month'
          GROUP BY t2agent 
          ORDER BY total DESC";
?>

NO NO NO NO NO!

 

This will not work at all -- you cannot rely on any value retrieved from a group by other than aggregate functions or the grouped column(s).

 

Please post your table structure.

[Zergman]

I don't have direct access to the db right now so gonna have to do it from memory

 

id  int(16)   auto_increment              
flagentTID  varchar                 
level1  varchar           
level2  varchar           
level3  varchar                
prov  varchar             
notes  longtext              
valid  varchar                 
resolution  varchar            
rescomments  varchar           
tdate  date     Yes                  
ttime  time     Yes                  
flmanager  varchar
tracking  varchar           
t2agent  varchar  

 

Now that I write this, I realize that the other data I want to display is in another table within the same DB.

 

The column t2agent in the data table has values that match the uname column in the users table

 

I want the t2agent counted from the data table but would also like to display the fname and lname columns from the users table.

 

Im so confused lol

[fenway]

This should help get you started:

 

SELECT t1.col1
, t1.col2
, t1.col3
, t1.tdate
, t1.t2agent
FROM data AS t1
INNER JOIN 
( SELECT tdate
,COUNT(t2agent) as t2agent
FROM data
WHERE MONTH(`tdate`) = '$month'
GROUP BY t2agent ) AS t2 USING( tdate, t2agent )