SELECT TRIM(TRIM( LEADING 'MD' FROM (TRIM( LEADING 'MOHAMMED' FROM TRIM(TRIM( LEADING 'MOHAMMAD' FROM TRIM(TRIM( LEADING 'MD' FROM (TRIM(LEADING 'DR' FROM (REPLACE(UCASE(o.customer_ref),'.', '')))))))))))) as "Name",
ROUND (SUM(
    if (d.stk_code REGEXP "^10|^31",if(d.stk_code like "3104",d.unit_price*(0-d.discount_percent),d.unit_price*(0.1-d.discount_percent)),
        if(d.stk_code REGEXP  "^50|^41",d.unit_price*(0.15-d.discount_percent),
           if(d.stk_code REGEXP "^11|^12|^13|^14|^15|^16|^19|^29",d.unit_price*(0.30-d.discount_percent),
               if(d.stk_code REGEXP "^60",d.unit_price*(0.25-d.discount_percent),d.unit_price*(0-d.discount_percent)
                 )
             )
          )
       )
    ),2) as Earn,
ROUND (SUM(
    if (d.stk_code REGEXP "^10|^31",if(d.stk_code like "3104",d.unit_price*(0-d.discount_percent),d.unit_price*(0.1-d.discount_percent)),
        if(d.stk_code REGEXP  "^50|^41",d.unit_price*0.15,
           if(d.stk_code REGEXP "^11|^12|^13|^14|^15|^16|^19|^29",d.unit_price*0.30,
                   if(d.stk_code REGEXP "^60",d.unit_price*0.25,d.unit_price*0
                 ))
          )
       )
    ),2) as Point, SUM(d.unit_price) as "Bill Total"
FROM `0_sales_order_details` as d
INNER JOIN (
    SELECT * FROM `0_sales_orders` Where (ord_date>"2014-09-30" and ord_date<"2014-11-01") and (contact_phone="" or contact_phone=NULL or contact_phone REGEXP "^01" )
    ) as o ON o.order_no = d.order_no
INNER JOIN (
    select * from 0_debtor_trans where type='13'
    ) AS tt ON tt.order_=o.order_no
Where tt.trans_no NOT REGEXP "4321|^4391|^4375|^4225|^4265|^4204|^4336|^4338|^4422|^4446|^4461|^4466|^4468|^4509|^4510|^4511|^4539|^4598|^4214|^4549|^4452|^4454|^4552|^4512|^4513|^4538|^4197|^4208|^4248|^4257|^4264|^4279|^4281|^4325|^4398|^4418|^4421|^4444|^4447|^4464|^ 4470|^4471|^4523|^4524|^4543|^4579"
Group by Name