COUNT() in MySQL Query

[iarp]

Kind of hard to describe my database table but i have a screenshot.

http://files.iarp.ca/images/ss.JPG

 

What i'm trying to do is count how many 1's are in each column.

 

Would i have to do something along the lines of..

$query3 = "SELECT COUNT(DAmodeo), COUNT(KArgyros), COUNT(NBrooks), COUNT(RDube), COUNT(TField), COUNT(HFord), COUNT(JHawkins), COUNT(CMacGregor), COUNT(BMetler), COUNT(RMillichamp), COUNT(WMoorehead), COUNT(KMurray), COUNT(DSabatino), COUNT(CSerrao), COUNT(MSnowball), COUNT(ZStewart) FROM " . DB_VOTES;

 

There must be a simpler way.

[beboo002]

check this script

 

<?php 
$link = mysql_connect('localhost', 'root', '');
if (!$link) {
    die('Not connected : ' . mysql_error());
}

// make foo the current db
$db_selected = mysql_select_db('timesheet', $link);
if (!$db_selected) {
    die ('Can\'t connect to database : ' . mysql_error());
}
$table=daycaltime;
$result=mysql_query("select * from $table");
$fields = mysql_list_fields(timesheet,$table);
$columns = mysql_num_fields($fields);
for ($i = 0; $i < $columns; $i++) {
$l=mysql_field_name($fields, $i);
$sql1="select count('$l') as abhi from daycaltime";
$result1=mysql_query($sql1,$link);
$row=mysql_fetch_array($result1);
echo $l;echo $row['abhi'];
}
?>

[fenway]

You just need to you SUM(IF(yourColumn='yourValue'),1,0).

[iarp]

I edited the code above:

$query = "SELECT * FROM " . DB_VOTES;
$result=mysql_query($query);
$fields = mysql_list_fields($result);
$columns = mysql_num_fields($fields);
for ($i = 0; $i < $columns; $i++) {
$l = mysql_field_name($fields, $i);
$sql1 = "SELECT COUNT('$l') AS cnt FROM " . DB_VOTES;
$result1 = mysql_query($sql1);
$row = mysql_fetch_array($result1);
echo $l;
echo $row['cnt'];
}

But i'm not getting anything outputted.

[fenway]

Or you could ignore my suggestion.

[iarp]

Never said i was going to ignore your suggestion, just(to my knowledge) it seems to be more work.

 

Using SUM(IF(yourColumn='yourValue'),1,0) instead of COUNT() in a query?

 

As is my code is:

 

<?php
$query = "SELECT COUNT(*) FROM " . DB_VOTES . " WHERE DAmodeo";
$result = mysql_query($query);
$dam = mysql_fetch_array($result);

$query = "SELECT COUNT(*) FROM " . DB_VOTES . " WHERE KArgyros";
$result = mysql_query($query);
$kar = mysql_fetch_array($result);

$query = "SELECT COUNT(*) FROM " . DB_VOTES . " WHERE NBrooks";
$result = mysql_query($query);
$nbr = mysql_fetch_array($result);

$query = "SELECT COUNT(*) FROM " . DB_VOTES . " WHERE RDube";
$result = mysql_query($query);
$rdu = mysql_fetch_array($result);

$query = "SELECT COUNT(*) FROM " . DB_VOTES . " WHERE TField";
$result = mysql_query($query);
$tfi = mysql_fetch_array($result);

$query = "SELECT COUNT(*) FROM " . DB_VOTES . " WHERE HFord";
$result = mysql_query($query);
$hfo = mysql_fetch_array($result);

$query = "SELECT COUNT(*) FROM " . DB_VOTES . " WHERE JHawkins";
$result = mysql_query($query);
$jha = mysql_fetch_array($result);

$query = "SELECT COUNT(*) FROM " . DB_VOTES . " WHERE CMacGregor";
$result = mysql_query($query);
$cma = mysql_fetch_array($result);

$query = "SELECT COUNT(*) FROM " . DB_VOTES . " WHERE BMetler";
$result = mysql_query($query);
$bme = mysql_fetch_array($result);

$query = "SELECT COUNT(*) FROM " . DB_VOTES . " WHERE RMillichamp";
$result = mysql_query($query);
$rmi = mysql_fetch_array($result);

$query = "SELECT COUNT(*) FROM " . DB_VOTES . " WHERE WMoorehead";
$result = mysql_query($query);
$wmo = mysql_fetch_array($result);

$query = "SELECT COUNT(*) FROM " . DB_VOTES . " WHERE KMurray";
$result = mysql_query($query);
$kmu = mysql_fetch_array($result);

$query = "SELECT COUNT(*) FROM " . DB_VOTES . " WHERE DSabatino";
$result = mysql_query($query);
$dsa = mysql_fetch_array($result);

$query = "SELECT COUNT(*) FROM " . DB_VOTES . " WHERE CSerrao";
$result = mysql_query($query);
$cse = mysql_fetch_array($result);

$query = "SELECT COUNT(*) FROM " . DB_VOTES . " WHERE MSnowball";
$result = mysql_query($query);
$msn = mysql_fetch_array($result);

$query = "SELECT COUNT(*) FROM " . DB_VOTES . " WHERE ZStewart";
$result = mysql_query($query);
$zst = mysql_fetch_array($result);
?>
<hr />
<table width="100%" height="100%" cellpadding="3" cellspacing="0" border="0">
<p><h1>Results</h1></p>
<tr><td width="50%" align="right">Domenic Amodeo</td>
	<td width="50%" align="left"><?php echo $dam[0]; ?></td></tr>
<tr><td width="50%" align="right">Kathy Argyros</td>
	<td width="50%" align="left"><?php echo $kar[0]; ?></td></tr>
<tr><td width="50%" align="right">Nancy Brooks</td>
	<td width="50%" align="left"><?php echo $nbr[0]; ?></td></tr>
<tr><td width="50%" align="right">Ray Dube</td>
	<td width="50%" align="left"><?php echo $rdu[0]; ?></td></tr>
<tr><td width="50%" align="right">Tina Field</td>
	<td width="50%" align="left"><?php echo $tfi[0]; ?></td></tr>
<tr><td width="50%" align="right">Helen Ford</td>
	<td width="50%" align="left"><?php echo $hfo[0]; ?></td></tr>
<tr><td width="50%" align="right">Joel Hawkins</td>
	<td width="50%" align="left"><?php echo $jha[0]; ?></td></tr>
<tr><td width="50%" align="right">Chuck MacGregor</td>
	<td width="50%" align="left"><?php echo $cma[0]; ?></td></tr>
<tr><td width="50%" align="right">Brian Metler</td>
	<td width="50%" align="left"><?php echo $bme[0]; ?></td></tr>
<tr><td width="50%" align="right">Ron Millichamp</td>
	<td width="50%" align="left"><?php echo $rmi[0]; ?></td></tr>
<tr><td width="50%" align="right">Wayne Moorehead</td>
	<td width="50%" align="left"><?php echo $wmo[0]; ?></td></tr>
<tr><td width="50%" align="right">Keith Murray</td>
	<td width="50%" align="left"><?php echo $kmu[0]; ?></td></tr>
<tr><td width="50%" align="right">Debbie Sabatino</td>
	<td width="50%" align="left"><?php echo $dsa[0]; ?></td></tr>
<tr><td width="50%" align="right">Claudio Serrao</td>
	<td width="50%" align="left"><?php echo $cse[0]; ?></td></tr>
<tr><td width="50%" align="right">Marshall Snowball</td>
	<td width="50%" align="left"><?php echo $msn[0]; ?></td></tr>
<tr><td width="50%" align="right">Zach Stewart</td>
	<td width="50%" align="left"><?php echo $zst[0]; ?></td></tr>
</table>

?>

[beboo002]

read my code carefully timesheetis my database name and daycaltime is table

 

$fields = mysql_list_fields($result);

 

here two value pass first is database name and second $result

 

like $fields = mysql_list_fields(databasename,$result);

[fenway]

Never said i was going to ignore your suggestion, just(to my knowledge) it seems to be more work.

 

Using SUM(IF(yourColumn='yourValue'),1,0) instead of COUNT() in a query?

It's not more work -- go back to modify your original query; for example:

 

SELECT SUM(IF(DAmodeo='1',1,0)) AS DAmodeoCNT, SUM(IF(KArgyros='1',1,0)) AS KArgyrosCNT FROM DB_VOTES

 

Obviously, you'll need to refer to the appropriate aliases in your php code, too.