An UPDATE question

[ririe44]

This is a Mysql query I'm wanting to do... and here's my stab at it, and want to run it by you...

 

I have two tables: entries, and accounts.  A column in entries is called "cat", another called "sub", and another called "amount".  In the accounts table I have "cat", "sub", and "balance".  The accounts table is rows of individual "cat"s and "sub"s that the entries table uses. 

 

So, I want to subtract the sum of all "cat"/"sub" from the entries table from the "balance" column in the accounts table.

 

For example in entries there might be: 

Home - Electricity - $35

....

Home - Electricity - $35

.... etc.

 

I want to subtract $70 (35+35) from the account balance.

 

I only need to run this once, and it seems like a lot of code to do something simple... So, here's my stab at it:

 

$tbl_name = 'accounts';
$tbl_name2 = 'entries';
$sub_sum = mysql_query("SELECT SUM(amount) FROM $tbl_name2 WHERE `cat` = '' AND `sub` = ''") or die(mysql_error()); 
$sub_fetch = mysql_fetch_assoc($sub_sum);
$sub_total = $sub_fetch['SUM(budget)'];

mysql_query("UPDATE `accounts` SET `balance` = (`balance`- '$sub_total') WHERE `sub` = '$sub' AND `cat` = '$cat'") or die(mysql_error());

 

Now I'm not sure if it's even a stab... any help?

[Ken2k7]

SELECT SUM(a.balance) - SUM(e.amount) AS `difference`
FROM accounts a
UNION entries e

?

[ririe44]

Maybe I should start simpler (for my sake)...  Let's say I just want to print all the distinct combinations of 'cat' and 'sub' from 'entries' with their totals....

 

$entry_data = mysql_query("SELECT SUM(amount) FROM $tbl_name WHERE ?__cat & sub are unique__?) or die(mysql_error());

Print "<th>Category:</th> <th>Sub-Category:</th> <th>Amount:</th></tr>";
while($info_entry = mysql_fetch_array( $entry_data ))
{
Print "<tr>";
Print "<td>".$info_entry['cat'] . "</td> ";
Print "<td>".$info_entry['sub'] . "</td> ";
Print "<td>$".$info_entry['amount'] . "</td> ";
}
Print "</table>";

[fenway]

Use COUNT(*) and GROUP BY cat, sub.

[ririe44]

but doesn't count just count the rows? 

 

So, what about this...

$query = ("SELECT 'cat' AND 'sub', SUM(amount) FROM products GROUP BY 'cat' AND 'sub'") or die(mysql_error());

[fenway]

Drop the single quotes... and there's no "AND" for a column list.

[ririe44]

So, like this?

 

$query = ("SELECT cat, sub, SUM(amount) FROM entries GROUP BY cat, sub") or die(mysql_error());

[fenway]

Yup.