How to do sql query inside count clause

public function successful_redemption_count_between_date($start_date, $end_date) {
        $d = DB::select(
            "select `prod`.`product_id` AS `product_id`,`prod`.`product_name` AS `product_name`,
                `op`.`ordered_qty` AS `ordered_qty`, count(`redm`.`campaign_id` WHERE DATE(created_on) = 'CURDATE()') AS `total_redeemed`,count(`redm`.`product_id`) AS `stocks_redeemed`,
                `cam`.`name` AS `campaign_name`, `bk`.`name` AS `bank_name` 
            from `product` `prod` 
                LEFT JOIN `redemption` `redm` on`prod`.`product_id` = `redm`.`product_id`
                JOIN `campaign_product` `cam_prod` on `prod`.`product_id` = `cam_prod`.`product_id`
                JOIN `campaign` `cam` on `cam_prod`.`campaign_id` = `cam`.`campaign_id`
                JOIN `bank` `bk` on `cam`.`bank_id` = `bk`.`bank_id`
                LEFT JOIN 
                (
                    select product_id, sum(quantity) as ordered_qty
                    from ordered_products
                    group by ordered_products.product_id
                ) as op
                    on op.product_id = prod.product_id
            group by `prod`.`product_id`"
        );
             
        return $d;
        
    }

it not working...I want to count amount of redemption for current date.Please help