[RESOLVED] Counting the occurence of a record

[Ali_baba]

Hello,

I am trying to count the occurence (no of times) of a record, and to display that how many times that record exists. Also the date of its first occurence and the date of its most recent occurence. (They are saved while entering that record in the database). How may I do that? This is killing me 🙁

It should be like this:

Part ID no: 1234
record occurence: 4 times
Date of first record: 25/02/06
Date of last occurence: 28/02/06

Here is my php code:

e:
$result = mysql_query("SELECT IDnum,record_date FROM DataCollection ORDER BY IDnum, record_date"); 
  while ($array1 = mysql_fetch_array($result1))  { 

Thanks in advance 🙂

[laserlight]

What do you mean by a record? Each record (i.e. row) should be unique in a table.

[Ali_baba]

Hello laserlight,

Yes I know that record should be unique respective to their ID (primary key) but here I am not talking about the primary key. I am taking about the IDnum, and how many times it is there 🙂

[laserlight]

You could GROUP BY IDnum

[Ali_baba]

Thanks laserlight for the reply. This approach might not work as I need to know the occurence of each IDnum

[laserlight]

You could try:

SELECT COUNT(IDnum) FROM DataCollection
	WHERE IDnum IN (SELECT DISTINCT(IDnum) FROM DataCollection);

[laserlight]

hmm... decided to test to see if my idea works... but of course it doesnt since COUNT() is an aggregate function.

You could do a hack with:

SELECT DISTINCT(IDnum) FROM DataCollection;

then run through the results of the above query with:

SELECT COUNT(IDnum) FROM DataCollection WHERE IDnum=$IDnum;

But there should be a way to do it with SQL alone...

[laserlight]

Okay, maybe this works:

SELECT IDnum, COUNT(IDnum) FROM DataCollection GROUP BY IDnum;

Should have taken my own advice earlier, lol 🙂

[Ali_baba]

Thanks laserlight

After understading your queries and "twisting" according to my needs 🙂, I did the following thing:

$result = mysql_query("SELECT COUNT(IDnum),IDnum, MIN(datevisit),MAX(datevisit) FROM DataCollection WHERE user='$user' GROUP BY IDnum ORDER BY IDnum");


 while ($array = mysql_fetch_array($result))  {


$visit_year1 = substr ($array['MIN(datevisit)'],0,4); 
$visit_month1 = substr ($array['MIN(datevisit)'],5,2); 
$visit_day1    = substr ($array['MIN(datevisit)'],8,2); 
$datevisit1 = "$visit_day1/$visit_month1/$visit_year1";

$visit_year2 = substr ($array['MAX(datevisit)'],0,4); 
$visit_month2 = substr ($array['MAX(datevisit)'],5,2); 
$visit_day2 = substr ($array['MAX(datevisit)'],8,2); 
$datevisit2 = "$visit_day2/$visit_month2/$visit_year2";

?>
    <tr valign="top">
    <td><? echo "&nbsp;".$array['IDnum']; ?></td>
    <td>&nbsp;<?echo $array['COUNT(IDnum)']; ?></td>
    <td><? echo "&nbsp;".$datevisit1; ?></td>  

    <td><? echo "&nbsp;".$datevisit2; ?></td> 

I will mark this thread resolved after showing and discussing this with my boss 😃