How to select 2 Sums in 1 query?

[Monkuar]

$DB->query("SELECT SUM(amount)  FROM gold_logs WHERE  from_id = {$ibforums->member['id']} GROUP BY from_id");
	   $data = $DB->fetch_row();

 

Okay, then I echo out

{$data['SUM(amount)']}

 

but I want to select the sum(amount) from "to_id"  ALSO! how can I add that into the query I dont  want to use 2 queries, thanks

[Monkuar]

Here's a update, it's not showing amount2?

 

$DB->query("SELECT SUM(amount)  FROM gold_logs WHERE  from_id = {$ibforums->member['id']} GROUP BY from_id UNION
SELECT SUM(amount) as amount2 FROM gold_logs WHERE  to_id = {$ibforums->member['id']} GROUP BY to_id");
	   $data = $DB->fetch_row();

 

When I echo out the data it shows:

 

Incoming Gold: <b>{$data['SUM(amount2)']}</b>

 

it doesn't show amount2? but shows 1?

[kicken]

You could use a UNION to do two queries in one call.  It would return two-rows, the first would be the sum of the from_id, the second the sum of the two_id.

 

SELECT SUM(amount) as amt FROM gold_logs WHERE  from_id = {$ibforums->member['id']} GROUP BY from_id
UNION ALL
SELECT SUM(amount) as amt  FROM gold_logs WHERE  to_id = {$ibforums->member['id']} GROUP BY to_id

 

Note if you alias the columns as above, you can reference them cleaner in your PHP code, such as:

$data['amt'];

[ManiacDan]

$DB->query("SELECT SUM(IF ( from_id = {$ibforums->member['id']}, amount, 0 ) ) fromSum,
SUM(IF ( to_id = {$ibforums->member['id']}, amount, 0 ) ) toSum
FROM gold_logs 
WHERE  from_id = {$ibforums->member['id']} OR to_id = {$ibforums->member['id']} ");
$data = $DB->fetch_row();
$from = $data['fromSum'];
$to = $data['toSum'];

-Dan

[Monkuar]

$DB->query("SELECT SUM(IF ( from_id = {$ibforums->member['id']}, amount, 0 ) ) fromSum,
SUM(IF ( to_id = {$ibforums->member['id']}, amount, 0 ) ) toSum
FROM gold_logs 
WHERE  from_id = {$ibforums->member['id']} OR to_id = {$ibforums->member['id']} ");
$data = $DB->fetch_row();
$from = $data['fromSum'];
$to = $data['toSum'];

-Dan

 

 

Wow, lol never knew you could use if's in mysql, rofl this is great code, saved for future use to, and i am going to study this,