need to count yes and no results in rows

[nade93]

Hi All

 

basically, I have a select that draws the desired results.

 

Within these results I then need to produce a number for if the case is closed.

 

The field value is 'case_open'

 

so if the case open is set to yes, the number of rows that say yes in that particular field and the same for no

 

can anyone give me any direction for this please?

[gassaz]

Use IF.

 

if(case_open='yes',1,0)

 

This will reurn 1 is yes and 0 when is not.

[nade93]

this only gives me the number 1

 

I need to make it count the number of results within my query that feature the "yes" for example

[GreenSmurf]

Use this count function:

http://www.techonthenet.com/sql/count.php

 

-B

[nade93]

thanks greensmurf, but thats not what im after

 

basically, trying to create a results page from a form and need two things that am really stuck on.

 

1. If the form value ="" then i need it to return all the results in that table field.

2. I need to then take those results and count certain values. For example, how many case_open fields have the "yes" value and then return that value as a number

 

Code for part 1.

$ethnic = $_REQUEST['ethnic'];
$gender = $_REQUEST['gender'];
$referral = $_REQUEST['referral'];
$age = $_REQUEST['age'];
$month = $_REQUEST['month'];
$year = $_REQUEST['year'];

$query  = "SELECT * from tbl WHERE";

if ((!empty($_POST['ethnic']))&&($_POST['ethnic'] != 'all')) { $query .= " ethnic_background like '". addslashes($_POST['ethnic'])."%' "; }

if ((!empty($_POST['gender']))&&($_POST['gender'] != 'all')) { $query .= " and gender like '". addslashes($_POST['gender'])."%' "; }


if ((!empty($_POST['age']))&&($_POST['age'] != 'all')) { $query .= " and age_range like '". addslashes($_POST['age'])."%' "; }

if ((!empty($_POST['month']))&&($_POST['month'] != 'all')) { $query .= " and date_received_month like '". addslashes($_POST['year'])."%' "; }

if ((!empty($_POST['year']))&&($_POST['year'] != 'all')) { $query .= " and date_received_year like '". addslashes($_POST['year'])."%' "; }

$result = mysql_query($query);
$numrows=mysql_num_rows($result);
if ($numrows == 0)
  {

  echo "<div class=\"heading\">Sorry, your search returned no results. Please go back and try again!</div>";

  }

 

I then run a loop i.e.

 <? while ($row = mysql_fetch_assoc($result)) {

?>

 

Then i need to return the results below in a table i.e.

<table><tr>
                <td valign="top" height="30" style="border-bottom:1px #ccc solid;" class="bodytext_table"><div align="left"> Number of IMHA cases opened</div></td>
	<td style="border-bottom:1px #ccc solid;"> <div align="right" style="color:#993366; font-size:12px; font-weight:600;">number here</div></td> </tr></table>

 

can someone shed some light please, its really urgent and need it tonight and have searched high and low for answers!!!

 

any help appreciated!

 

thanks

[artacus]

SELECT SUM(CASE WHEN case_open = 'yes' THEN 1 ELSE 0 END) AS open_cases
...

[DavidAM]

The SELECT SUM will work if you want to make another trip to the database.  However why not just count them while you are outputting them to the table?

 

$open_count = 0
while ($row = mysql_fetch_assoc($result)) {
   if ($row['case_open'] == 'yes') $open_count++;
}

 

 

[fenway]

There's no "other trip"... same query.

[DavidAM]

right, I guess I should actually RTFP.  I thought OP was listing all the data to the page in the loop and wanted to add the count at the end.  If there is no need to list the data, then the SELECT SUM is definitely the way to go.  Uses less resources all the way around.