premi premi - 3 months ago 8
jQuery Question

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

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

Answer

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

HAVING SUM(amtreceived) > 5000