I have a table lead_submission
which contains value of a user in a specific format like
agent_name qa_details
xxx 1001:|1083:|504:Yes|1009:|
ccc 504:Yes|1083:No|1008:|1009:|
now I want to get the count of only say 504:Yes
from two rows
these values are coming from another table paid_response
qno paid_response
504 Yes
1083 No
1083 Possibly
<?php
//db connection goes here
$sql=mysql_query("select qno,paid_response from paid_response where qno='504' ");
while($rows=mysql_fetch_array($sql)) {
$exqnos= $rows['qno'].'|'.$rows['paid_response'];
}
list($key,$val)=explode('|',$exqnos);
$exqno[$key]=$val;
foreach($exqno as $qno=>$value) {
$string .="qa_details LIKE '%|$qno:$value|%' ";
}
$sql=mysql_query("SELECT count(agent_name) as agent_cnt,count($string) as ppicount FROM `lead_submission` WHERE $string "); ?>
<table border="1">
<thead>
<tr>
<th>CountAgent</th>
<th>504-COUNT</th>
</tr>
<?php
while($row=mysql_fetch_array($sql)) { ?>
<tr style="color:red" >
<td><?php echo $row['agent_cnt']; ?></td>
<td><?php echo $row['ppicount']; ?></td>
</tr>
<?php
}
?>
Now by doing this i am getting count as 2 for 504:Yes
CountAgent 504-COUNT
2 2 //as u can see that `504:Yes` has occured two times in lead_submission table.
my point is how can i also count another combination say 1083:No
and show count in the same table
NB:- cant we just fetch the combination like `504:Yes` or `1083:No` or `1083:Yes` from paid_response table to maintain stability so that i dont have to change the query everytime.
CountAgent 504-COUNT 1083-Count
2 2 1 //how to get this count `1083:No` . as u can see it only appeared 1 times in `lead_submission` table
Try this:
SELECT COUNT(DISTINCT ls.agent_name),
SUM(CASE WHEN pr.qno = 504 AND pr.paid_response = 'Yes' THEN 1 ELSE 0 END) AS '504-Count',
SUM(CASE WHEN pr.qno = 1083 AND pr.paid_response = 'No' THEN 1 ELSE 0 END) AS '1083-Count'
FROM lead_submission ls
INNER JOIN paid_response pr
ON CONCAT('|', ls.qa_details, '|') LIKE CONCAT('%|', pr.qno, ':', pr.paid_response , '|%');
Check the SQL FIDDLE DEMO
OUTPUT
| COUNTAGENT | 504-COUNT | 1083-COUNT |
|------------|-----------|------------|
| 2 | 2 | 1 |
::EDIT::
First execute below query
SELECT GROUP_CONCAT('SUM(CASE WHEN pr.qno = ', qno, ' AND pr.paid_response = ''', paid_response,''' THEN 1 ELSE 0 END) AS ''', qno, '-Count''')
FROM paid_response;
Use output of this query and build your final query as below:
query = 'SELECT COUNT(DISTINCT ls.agent_name), ' + outputOfAboveQuery + ' FROM lead_submission ls NNER JOIN paid_response pr ON CONCAT('''|''', ls.qa_details, '''|''') LIKE CONCAT('''%|''', pr.qno, ''':''', pr.paid_response , '''|%''');';
Execute this string in your code so you can get dynamic query to fetch the counts
<?php
$con = mysql_connect('localhost', 'root', '');
mysql_select_db('test',$con);
function countIt($checkArray)
{
$checkVals = explode(',', $checkArray);
foreach($checkVals AS $val){
list($qNo, $pRes) = explode(':', $val);
$query = mysql_query("SELECT * FROM `paid_response` WHERE `qno`='$qNo' AND `paid_response`='$pRes'");
if(mysql_num_rows($query) > 0){
$query = mysql_query("SELECT * FROM `lead_submission` WHERE `qa_details` LIKE '%$val%'");
$countArray[$val] = mysql_num_rows($query);
} else {
$countArray[$val] = 0;
}
}
foreach($countArray AS $key=>$val){
echo $key . ' => ' . $val . "<br/>";
}
}
echo countIt('504:yes,1083:no,1083:yes,1083:possibly,504:no');
Try this Man!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With