Check where condition with sum(amount) using mysql and codeigniter

Question

I am using codeigniter framework with mysql. I want to display records with sum(amtreceived)>50000. otherwise i want to check with as value that means sum(iman_sub_product_payment_details.amtreceived) as amount.Is it possible to check like this. Anybody please help me. Below is my query

$sql='SELECT pay_date,sub_ins_name as name, sum(iman_sub_product_payment_details.amtreceived) as amount, sum(iman_sub_product_payment_details.refundedamt) as refamount,iman_sub_product_payment_details.subscriber_id as subid FROM iman_subscriber_insitution_details LEFT JOIN `iman_sub_product_payment_details` ON `iman_sub_product_payment_details`.`subscriber_id`=`iman_subscriber_insitution_details`.`sub_institute_id`';
    if(!empty($data['fromdate'])){
        $sql.=' where iman_sub_product_payment_details.pay_date >= date("'.$fromdate.'")';

    }if(!empty($data['todate'])){
        $sql.=' and iman_sub_product_payment_details.pay_date <= date("'.$todate.'")';
    }if($data['comboval']!=0){
        $sql.=' and iman_sub_product_payment_details.combostatus ="1"';
    }if($data['from_amount']!=""){
            $sql.=' and amtreceived >="'.$data['from_amount'].'"';
        }
        if($data['to_amount']!=""){
            $sql.=' and amtreceived <="'.$data['to_amount'].'"';
        }
        if($data['currency']!=0){
            $sql.=' and iman_sub_product_payment_details.cur_id ="'.$data['currency'].'"';
        }
    $sql.=' group by iman_subscriber_insitution_details.sub_institute_id';
    $sql.=' UNION 
    SELECT pay_date,agentname as name,sum(iman_sub_product_payment_details.amtreceived) as amount,sum(iman_sub_product_payment_details.refundedamt) as refamount,iman_sub_product_payment_details.subscriber_id as subid  FROM iman_agent_details 
    LEFT JOIN `iman_sub_product_payment_details` ON `iman_sub_product_payment_details`.`dealer_id`=`iman_agent_details`.`agentid` ';

    if(!empty($data['fromdate'])){
        $sql.=' where iman_sub_product_payment_details.pay_date >= date("'.$fromdate.'")';

    }if(!empty($data['todate'])){
        $sql.=' and iman_sub_product_payment_details.pay_date <= date("'.$todate.'")';
    }if($data['comboval']!=0){
        $sql.=' and iman_sub_product_payment_details.combostatus ="1"';
    }if($data['from_amount']!=""){
        $sql.=' and amtreceived >="'.$data['from_amount'].'"';
    }
    if($data['to_amount']!=""){
        $sql.=' and amtreceived <="'.$data['to_amount'].'"';
    }
    if($data['currency']!=0){
        $sql.=' and iman_sub_product_payment_details.cur_id ="'.$data['currency'].'"';
    }
    $sql.=' group by iman_agent_details.agentid';
    $sql.=' order by name asc';
    $query=$this->db->query($sql);

I did't know i want to use having in this situation. I sum many records union all etc. so i meant as a new question — premi, Aug 12 '16 at 11:54

score 2 · Accepted Answer · answered Aug 12 '16 at 11:29

2

What you need is a HAVING clause after the GROUP BY. Such as :

HAVING SUM(amtreceived) > 5000

answered Aug 12 '16 at 11:29

Fred

36
1

Check where condition with sum(amount) using mysql and codeigniter

1 Answers1