Can I Join tables and use AVG in the same Query

I'm new to php and MySQL and I'm having a problem trying to work this one out.

I have this query

SELECT * FROM comments, supps 
WHERE supps.tutorialid = comments.tutorialid 
  AND category='1' 
ORDER BY $orderby $sort 
LIMIT $startrow, $limit

Which is working fine, but the problem is I want to also include the AVG of a column from commen开发者_高级运维ts table and I can't seem to get it to work with this query. Can anyone help me

To use aggregate functions, you need to use the 'GROUP BY' clause. Typically goes before 'ORDER BY'. Note: if you want to use group by, you need to define your attributes in the SELECT clause, or else things can become a bit sticky.

For example:

SELECT id, avg(value) FROM comments c
INNER JOIN supps s
    on c.tutorialid = s.tutorialid
WHERE category = 1
GROUP BY id
etc...

Implicit SQL joins considered harmful
First of all please do not use implicit join syntax. It belongs in 1989 please bury it there.

It looks like you have an SQL-injection leak
if you use php and you don't put your injected variables in single quotes ' mysql_real_escape_string() will not work!

And you will be at risk of SQL-injection

Lastly you are injecting column names into your query. mysql_real_escape_string() will not protect you when doing that, neither will use PDO or anything else. You will need to check the column names against a pre-appoved white list on order to not fall victim to SQL injection attacks. See here for more info:
If you inject $vars into a limit clause mysql_real_escape_string() does not work because MySQL does not see these as values, but as literals you need to cast them into integers to make it safe.

Rewrite the query into and preceding php code to:

//stricly speaking not needed here, because we only accept integers
//but I would use them anyway, just in case you change the query and the
//role of these vars change.
$limit = mysql_real_escape_string($_POST['limit']);
$startrow = mysql_real_escape_string($_POST['startrow']);

//Check injected column, table and database names against a whitelist!
$allowed_columns = array('column1', 'column2');
$orderby = $_POST['orderby'];
$sort = $_POST['sort'];
if !(in_array($orderby, $allowed_columns)) {die ("no way Jose");}
if !(in_array($sort, $allowed_columns)) {die ("no way Jose");}

//Make 100% sure injected limit parameters are integers.
if !((int) $startrow == $startrow) {die ("no way Jose");}
if !((int) $limit == $limit) {die ("no way Jose");}

$query = "SELECT s.*, c.* ,AVG(c.field1) as average 
          FROM comments c
          INNER JOIN supps s ON (s.tutorialid = c.tutorialid)
          WHERE category = '1'
          GROUP BY s.id 
          ORDER BY `$orderby`, `$sort`  //surround injected column names with ` 
          LIMIT '$startrow', '$limit' ";

SQL injection protection recap
1. Always use mysql_real_escape_string() to escape data that comes from outside. (or even better use PDO)
2. Surround injected $var values in your queries with ' single quotes; if you don't escaping with mysql_real_escape_string() will not work.
3. check injected table, column and database names against a whitelist.
4. Surround $vars used for table, column and database names in backticks `; this is not a security measure, but your query will fail if you use reserved words, numbers of (shudder) field names with spaces, backticks fix this.
5. Test to see if injected $vars in your limit clause are integers.

Fail to do any of those things and your server will the pwned!.

Back to your question
My fingers hurt, but other people have kindly answered your question.

Links
SQL-injection in general: How does the SQL injection from the "Bobby Tables" XKCD comic work?
Whitelisting for injected column names: How to prevent SQL injection with dynamic tablenames?

Yes, it is possible. Look for example on the SQL Wikibooks tutorial, where the following examples of using AVG() on two different tables were presented:

/* Without INNER JOIN */
 SELECT AVG(Price), Manufacturers.Name
   FROM Products, Manufacturers
   WHERE Products.Manufacturer = Manufacturers.Code
   GROUP BY Manufacturers.Name;

 /* With INNER JOIN */
 SELECT AVG(Price), Manufacturers.Name
   FROM Products INNER JOIN Manufacturers
   ON Products.Manufacturer = Manufacturers.Code
   GROUP BY Manufacturers.Name;