mysql query help, take total sum from a table, and based on discount value on another table calculate the gross_total

Question

hi

I have a table called invoices:

`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) 
) 

each invoice has items that are stored in invoice_items table :

 `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
) ;

and tax table

`si_tax` (
  `tax_id` int(11),
  `tax_description` varchar(50) ,
  `tax_percentage` decimal(25,6) ,
  `type` varchar(1),
  `tax_enabled` varchar(1) 
) ;

here is what I want to do
step 1: get the sum_total of the invoice Items for a speciefic invoice
step 2: calculate the discount, in the invoice table I have a discount_type field :
if its equal to 0 , then there will be no discount
if its equal to 1 , the discount value will be stored in the discount field
if its equal to 2 , the discount is a percentage of sum_total

step 3: calculate the taxes based on inv_tax_id
based on the tax id , I will look in the tax table , get the tax_percentage and multiply it by the (sum_total - discount)

in short here is the equation
$gross_total = $sum_total - $disount + taxes

Answer 1

Not sure why you would want to do this all in MySQL, but here is the query:

SELECT (SELECT `tax_percentage` FROM `si_tax` WHERE `tax_id` = `si_invoices`.`inv_tax_id`) * (`sum_total` - CASE `discount_type` WHEN 1 THEN `discount` WHEN 2 THEN `sum_total` * `discount` / 100 ELSE 0 END) AS `gross`
FROM `si_invoices`
JOIN (SELECT SUM(`total`) AS `sum_total` FROM `si_invoice_items` WHERE `invoice_id` = `si_invoices`.`id`) AS `t`
WHERE `id` = ?

Answer 2

FINALLY!!!!! I got it to work

here is the query:

     SELECT sum( iv.inv_total - iv.inv_discount + iv.taxes ) FROM
 (
SELECT 
        (SELECT sum(ii.total) FROM si_invoice_items ii
            where ii.invoice_id = v.id) as inv_total ,
        (
            SELECT
                CASE v.discount_type
                    WHEN 1 THEN v.discount
                    WHEN 2 THEN (v.discount / 100) * ( SELECT inv_total ) 
                    ELSE 0
                END
        ) AS inv_discount,

        (SELECT 
            CASE t.type
                WHEN '$' THEN t.tax_percentage
                WHEN '%' THEN ( t.tax_percentage / 100 ) * (SELECT inv_total - inv_discount)
                ELSE 0
            END
        FROM si_tax as t
        WHERE t.tax_id = inv_tax_id
        ) AS taxes


    FROM 
        si_invoices v
    ) iv

elegant , its a beauty isn't she.. :D

thanks Arnie I appreciate your help, and Mike Pelley it would've been better if you gave a positive comment or a little help rather than acting as "man of the law"