Using OR in mysql query for multiple variables

poldings
poldings used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008
Commented:
When you combine AND and OR in the WHERE clause, you need to use parantheses:
$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"; 

Open in new window

Top Expert 2008

Commented:
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

Top Expert 2008

Commented:
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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial