Jump to content

COUNT() in MySQL Query

iarp

By iarp
in MySQL Help

 Share

Recommended Posts

iarp Regular Member

iarp

Posted
Posted

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.

Link to comment
Share on other sites
beboo002 Member

beboo002

Posted
Posted

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'];
}
?>

Link to comment
Share on other sites
iarp Regular Member

iarp

Posted
  • Author
Posted

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.

Link to comment
Share on other sites
iarp Regular Member

iarp

Posted
  • Author
Posted

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>

?>

Link to comment
Share on other sites
beboo002 Member

beboo002

Posted
Posted

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);

Link to comment
Share on other sites
fenway Advanced Member

fenway

Posted
Posted

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.

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.