SQL syntax

Is there a way to say select * and still use sum(amount)?

Also, it only gives one line with the total for each entityID. I want all the transactions listed but a total at the end of the group. How would I do that?

I would use your original query and then using a subquery looks mine with the exception that you will add in blank/empty strings or null for columns before and after the amount in your query then UNION ALL the two queries and then order by your entityID and should end up with all you data then a line like this.

entityID, '', '', '', 2000.00, '', '', '', ''

Hope that helps.

can you give me an example of how to do this?

{I am not a PHP master, so i apologize ahead of time}
Say you have 6 columns in your select.

$sql ="SELECT * FROM (SELECT entityID, col2, col3, col4, col5, amount FROM $table_name1 WHERE trans_date BETWEEN '$startDate' AND '$endDate'";
$sql = $sql + " UNION SELECT entityID, '', '', '', '', SUM(amount) FROM $table_name1 WHERE trans_date BETWEEN '$startDate' AND '$endDate' GROUP BY entityID) ORDER BY trans_date, entityID";

I did this and got a SQL syntax error.
$sql ="SELECT * FROM (SELECT entityID, trans_date, type, catID, cat_subID, payeeID, amount FROM $table_name1 WHERE trans_date BETWEEN '$startDate' AND '$endDate'";
$sql = $sql + " UNION SELECT entityID, '', '', '', '','','', SUM(amount) FROM $table_name1 WHERE trans_date BETWEEN '$startDate' AND '$endDate' GROUP BY entityID) ORDER BY  entityID";

I took out the second line did this:
$sql ="SELECT * FROM (SELECT entityID, trans_date, type, catID, cat_subID, payeeID, amount FROM $table_name1 WHERE trans_date BETWEEN '$startDate' AND '$endDate')";

Now I get an error message saying Every derived table must have its own alias.

$sql ="SELECT * FROM (SELECT entityID, trans_date, type, catID, cat_subID, payeeID, amount FROM $table_name1 WHERE trans_date BETWEEN '$startDate' AND '$endDate'";
$sql = $sql + " UNION SELECT entityID, '', '', '', '','','', SUM(amount) FROM $table_name1 WHERE trans_date BETWEEN '$startDate' AND '$endDate' GROUP BY entityID) derived ORDER BY  entityID";

Add in an alias for the inner query like derived.

I added AS sb at the end and the query executed but gave no results.

I took out the second one because the first one isn't working.  Once that one works, then I'll add the second query above.

Like I said I am not a PHP person, but don't you need to have the variables outside of the string literal, something like this:

$sql ="SELECT * FROM (SELECT entityID, trans_date, type, catID, cat_subID, payeeID, amount FROM ".$table_name1." WHERE trans_date BETWEEN '".$startDate."' AND '".$endDate."')";

Sorry and yes derived table needs alias:
$sql ="SELECT * FROM (SELECT entityID, trans_date, type, catID, cat_subID, payeeID, amount FROM ".$table_name1." WHERE trans_date BETWEEN '".$startDate."' AND '".$endDate."') As sb";

I've gotten the first statement to work.  Can I have multiple conditions in the where clause like:
  $sql ="SELECT * FROM $table_name1 WHERE (trans_date BETWEEN '$startDate' AND '$endDate') AND (entityID = '$indEntity'), ORDER BY  trans_date";
I'm getting a syntax error. I'm trying the second query now. I've increased the points for your efforts.

You can.  Think the syntax error is with the comma (,) following the last condition of the where clause right before the ORDER BY statement.

The second sql statement isn't working. I think it doesn't like the
$sql = $sql + "

might be "." instead of "+".

I was trying to concatenate the strings.  I just used the wrong operator for PHP: http://us3.php.net/language.operators.string.

Also tried changing the first one to match of the number of fields to
$sql ="SELECT entityID, type, catID, cat_subID, payeeID, trans_date, amount FROM $table_name1 WHERE trans_date BETWEEN '$startDate' AND '$endDate' ORDER BY  entityID, trans_date, type, catID,cat_subID";