asked on

Using OR in mysql query for multiple variables

I need to run an accounts summary of expenses across specific departments with an individual total for each grouped department plus an overall total for all grouped departments.

I have set it into two queries as below.

The result I get is shown in the screenshot and as you can see, the overall totals do not add up correctly.

Can anyone see where my code is going wrong?

<?
// get main groups and totals
$sqlAE = "SELECT sum(paidin),sum(paidout),sum(vat),department,paidin,paidout,vat FROM banking WHERE datepaid BETWEEN $period1 AND $period2 AND department='Salary' OR department='Training' OR department='Travel' AND  paidout>0.01 GROUP BY department"; 
$resultAE = mysql_query($sqlAE);
while ($newArrayAE = mysql_fetch_array($resultAE)) {
$depotAE = $newArrayAE['department'];
$paidoutsumAE = number_format($newArrayAE['sum(paidout)'], 2, '.', '');
$vatsumAE = number_format($newArrayAE['sum(vat)'], 2, '.', '');
$netsumAE = number_format($paidoutsumAE - $vatsumAE, 2, '.', '');
 
// get individual totals
$query1AE = "SELECT sum(paidin),sum(paidout),sum(vat) FROM banking WHERE datepaid BETWEEN $period1 AND $period2 AND department='$depotAE' AND paidout>0.01"; 
$result1AE = mysql_query($query1AE); 
while($row1AE = mysql_fetch_array($result1AE)){ 
$paidoutsum1AE = number_format($row1AE['sum(paidout)'], 2, '.', '');
$vatsum1AE = number_format($row1AE['sum(vat)'], 2, '.', '');
$netsum1AE = number_format($paidoutsum1AE - $vatsum1AE, 2, '.', '');
} 
?>
show results
<? } ?>

Open in new window

accounts.jpg

ASKER CERTIFIED SOLUTION

Roger Baklund

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roger Baklund

This could also be written without OR:

$sqlAE = "SELECT sum(paidin),sum(paidout),sum(vat),department,paidin,paidout,vat FROM banking 
  WHERE 
    datepaid BETWEEN $period1 AND $period2 AND 
    department IN ('Salary','Training','Travel') AND  
    paidout>0.01 
  GROUP BY department";

Open in new window

Roger Baklund

Another thing, you should remove the columns paidin,paidout and vat:

$sqlAE = "SELECT sum(paidin),sum(paidout),sum(vat),department FROM banking 
  WHERE 
    datepaid BETWEEN $period1 AND $period2 AND 
    department IN ('Salary','Training','Travel') AND  
    paidout>0.01 
  GROUP BY department";

Open in new window

poldings

ASKER

Thanks. It's so obvious now you've pointed it out. Thanks again.