Get 3 totals seperately problem

[imperium2335]

Could someone tell me why this isnt working:

 

$branchPoundsResult = mysql_query("(SELECT branch, currency, sum(absoluteTotal) as poundTotal
								   FROM invoices_out
								   WHERE branch = '$currentBranch'
								   AND currency = '£')
								   UNION
								   (SELECT branch, currency, sum(absoluteTotal) as dollarTotal
								   FROM invoices_out
								   WHERE branch = '$currentBranch'
								   AND currency = '$')
								   UNION
								   (SELECT branch, currency, sum(absoluteTotal) as euroTotal
								   FROM invoices_out
								   WHERE branch = '$currentBranch'
								   AND currency = '€')
								   ")or die(mysql_error()) ;

 

The desired output is three rows for whatever the current branch is, each with a total in pounds, euros and dollars, but im getting nothing out

[Maq]

The desired output is three rows for whatever the current branch is, each with a total in pounds, euros and dollars, but im getting nothing out

You're not outputting anything.  Is there more code?  If the or die() is not printing anything, than your query is syntactically correct.

[imperium2335]

Sorry, this is what follows it (branchPoundsResult is just a typo):

 

$currencyRows = mysql_fetch_assoc($branchPoundsResult) ;

echo $currencyRows ['poundTotal'] . "<br />"; // The total in euros.
echo $currencyRows ['dollarTotal'] . "<br />"; // The total in euros.
echo $currencyRows ['euroTotal'] . "<br />"; // The total in euros.

exit() ;

 

The exit is there because this is part of a loop (there are more branches it will go through, this is just for testing to see if I can get the right output first).

[mikosiko]

could you post more of your code?  your SELECT looks no right to me, but I need to know what exactly are you trying to do to offer some further advice.

[imperium2335]

Hi,

 

Here is the full code for this stage:

 

include("currency-convert.php") ;
include("dbconnectlocal.php") ;

$invOutBranchesResults = mysql_query("SELECT DISTINCT branch
									  FROM invoices_out") ; // Get the branches that have invoices out to customers one by one.
while($branchRow = mysql_fetch_assoc($invOutBranchesResults)) {
// For each branch...
echo $currentBranch = $branchRow['branch'] ;
// For this branch, we now add up all their outbound invoices for each currency, for $s and €s we need to convert to pounds, then add up the totals for each currency, to give us the total in pounds.

$branchPoundsResult = mysql_query("SELECT branch, currency, sum(absoluteTotal)
								   FROM invoices_out
								   WHERE branch = '$currentBranch'
								   AND currency = '£'
								   GROUP BY branch
								   ")or die(mysql_error()) ;
$branchDollarsResult = mysql_query("SELECT branch, currency, sum(absoluteTotal)
								   FROM invoices_out
								   WHERE branch = '$currentBranch'
								   AND currency = '$'
								   GROUP BY branch
								   ")or die(mysql_error()) ;
$branchEurosResult = mysql_query("SELECT branch, currency, sum(absoluteTotal)
								   FROM invoices_out
								   WHERE branch = '$currentBranch'
								   AND currency = '€'
								   GROUP BY branch
								   ")or die(mysql_error()) ;
// Get all the £,$,€ invoice totals for this branch, returning one row which is the total.
$poundsRow = mysql_fetch_assoc($branchPoundsResult) ;
$dollarsRow = mysql_fetch_assoc($branchDollarsResult) ;
$eurosRow = mysql_fetch_assoc($branchEurosResult) ;

$pounds = $poundsRow['sum(absoluteTotal)'] ; // The total in pounds.
$dollars = $dollarsRow['sum(absoluteTotal)'] ; 
$euros = $eurosRow['sum(absoluteTotal)'] ;

// Convert the dollars and euros into pounds...

$dinPounds = cConvert($dollars, 'dollar') ;
$einPounds = cConvert($euros, 'euro') ;

$finalPoundsTotal = $pounds + $dinPounds + $einPounds ;
}

 

As you can see, at the moment I'm using three separate quires for the different currency. I'm thinking there must be a way to do this with just one query.

 

Thanks for your input so far.

[mikosiko]

that was my guess 

 

this select should replace ALL the select that you have (even the first one)

 

SELECT branch, 
       sum(if(currency = '£',absoluteTotal,0)) AS branchpound,
       sum(if(currency = '$',absoluteTotal,0)) AS branchdollar,
       sum(if(currency = '€',absoluteTotal,0)) AS brancheuro
  FROM invoice_out
  GROUP BY branch	

 

the rest is only display... try it.

[imperium2335]

Your a genius thank you! I knew it had something to do with IFs but couldn't figure it out, been on this problem for three days!

 

Can learn a lot from reverse engineering your solution too, as I will need to do this kind of thing again now for something else, so thanks!