sharvanaz sharvanaz - 6 months ago 18
MySQL Question

How to check before COUNT operation?

1) I have a table named "plan_info_upload" which is like:

site_id site_name 2G_bw 3G_bw route_path
1 MBCGP1 11 0 abc>bcd>cde
2 BOPBG2 12 22 mnl>khl>jhg
3 BOPCB1 11 0 abc>bcd>khl>bbo
4 BOSBB1 0 25 bops>doc>abc>bcd>jkl


I have to find how many 2g & 3g bw & total 2g & 3g bw of a unique_hop from the routh_path. but if its 2G_bw or 3G_bw is zero, it do not count it. but I do not understand how to check it if its value is 0 or not before count.

My code is here:

<?php
$unique_hop=$_POST['unique_hop'];
$i = 0;
$sql4 = "SELECT COUNT(2G_bw) AS no_of_2G, SUM(2G_bw) AS total_2G_bw, COUNT(3G_bw) AS no_of_3G, SUM(3G_bw) AS total_3G_bw FROM plan_info_upload WHERE route_path LIKE '%$unique_hop%'";
$result = $conn->query($sql);

if ($result->num_rows > 0)
{
while($row = $result->fetch_assoc())
{
$no_of_2G = $row['no_of_2G'];
$total_2G_bw = $row['total_2G_bw'];
$no_of_3G = $row['no_of_3G'];
$total_3G_bw = $row['total_3G_bw'];
?>

<tr>
<td><?php echo ++$i;?></td>
<td><?php echo $unique_hop;?></td>
<td><?php echo $no_of_2G;?></td>
<td><?php echo $total_2G_bw;?></td>
<td><?php echo $no_of_3G;?></td>
<td><?php echo $total_3G_bw;?></td>
</tr>
<?php
}
}
?>


from 1st table, if I select unique_hop "abc>bcd" , then 3 routh_path have this string & count & sum their existing 2G_bw & 3G_bw.

My desire output will be:

unique_hop no_of_2G total_2G_bw no_of_3G total_3G_bw
abc>bcd 2 22 1 25


But the output is coming like that:

unique_hop no_of_2G total_2G_bw no_of_3G total_3G_bw
abc>bcd 3 22 3 25


so how to check before COUNT operation? please anybody help me to sort out the problem.

Answer

Try this:

SELECT COUNT(nullif(2G_bw, 0)) AS no_of_2G, SUM(2G_bw) AS total_2G_bw, COUNT(nullif(3G_bw, 0)) AS no_of_3G, SUM(3G_bw) AS total_3G_bw FROM plan_info_upload WHERE route_path LIKE '%$unique_hop%'";