title: mysql query help, take total sum from a table, and based on discount value on another table calculate the gross_total
body: <p>I have a table called invoices:</p>
  
  <pre><code>`si_invoices` (
    `id` int(10) ,
    `biller_id` int(10) ,
    `customer_id` int(10) ,
    `type_id` int(10) ,
    `inv_tax_id` int(10) ,
    `date` date ,
    `unreg_customer` tinyint(1) ,
    `discount` decimal(10,2) ,
    `discount_type` tinyint(1) 
  ) 
  </code></pre>
  
  <p>each invoice has items that are stored in invoice_items table :</p>
  
  <pre><code> `si_invoice_items` (
    `id` int(10) ,
    `invoice_id` int(10) ,
    `quantity` int(10) ,
    `product_id` int(10) ,
    `warehouse_id` int(10) ,
    `unit_price` decimal(25,2) ,
    `total` decimal(25,2) ,
    `description` text
  ) ;
  </code></pre>
  
  <p>and tax table</p>
  
  <pre><code>`si_tax` (
    `tax_id` int(11),
    `tax_description` varchar(50) ,
    `tax_percentage` decimal(25,6) ,
    `type` varchar(1),
    `tax_enabled` varchar(1) 
  ) ;
  </code></pre>
  
  <p>here is what I want to do<br />
  <strong>step 1:</strong> get the sum_total of the invoice Items for a speciefic invoice<br />
  <strong>step 2:</strong> calculate the discount, in the invoice table I have a discount_type field :<br />
           if its equal to 0 , then there will be no discount<br />
           if its equal to 1 , the discount value will be stored in the discount field<br />
           if its equal to 2 , the discount is a percentage of sum_total <br /></p>
  
  <p><strong>step 3:</strong> calculate the taxes based on inv_tax_id<br />
    based on the tax id , I will look in the tax table , get the tax_percentage and multiply it by the (sum_total - discount)<br /></p>
  
  <p>in short here is the equation<br />
  $gross_total = $sum_total - $disount + taxes<br /></p>