Solved

Count  number found in database

Posted on 2011-02-10
8
332 Views
Last Modified: 2012-05-11
Hi. I have three columns in my table  id,num,diff  with the id col just being auto incremented. The numbers in the table are like this:

num                                       diff

1                                            11                        
1                                            4
1                                            8
1                                            4
1                                            4
1                                            6
1                                            5
2                                            4
2                                            5
2                                            2
2                                            3
2                                            10
2                                            13
2                                            1
3                                            5                                      
3                                           1
3                                           7
3                                           4
etc

I am trying to run a query that will  give a count of each diff # for every num. For example for the number 1 in the num column, there 4 fours,1 eight, 1 six, 1 five and 1 eleven. I want to do this for each number in the num column. I was using "select count(diff) in my query, but I can't get it to produce the correct output. It will pull some numbers correctly but not others. Here is the script

$rs3 = mysql_query("select * from ccc");
while($row3=mysql_fetch_array($rs3))
{
echo $row3['num']." &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;".$row3['diff']."<br/>";
}
$myar = range(1,56);
foreach ($myar as $b)
 {
$rs2 = mysql_query("select count(diff) as mycount,num from ccc where  (diff  = $b and num = $b)");
while($row2=mysql_fetch_array($rs2))
{
 $num_rows2 = mysql_num_rows($rs2);

  $num2 = $row2['num'];
   $mycount2 = $row2['mycount'] ;
  if ($row2['mycount'] > 0)
 {
 echo "In your selected date range, the number $num2"."&nbsp;". "skipped "."$b &nbsp;drawings and returned on&nbsp;".$row2['mycount']." "."different occasions."."<br />";
}
else
{
echo "In your selected date range, the number $num2"."&nbsp; "."skipped "."$b &nbsp;drawings before returning ".'No'." "."Times"."<br />";
}
}
 }


Any help would be appreciated.








0
Comment
Question by:phillyrob817
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 5

Expert Comment

by:LLMorrisson
ID: 34867635
Couldn't you just do this;

SELECT num, SUM(diff) as diff
FROM table
GROUP BY num;
0
 
LVL 5

Expert Comment

by:LLMorrisson
ID: 34867648
Oh, my bad, I misunderstood. Sorry ;)
0
 
LVL 22

Accepted Solution

by:
Thomasian earned 125 total points
ID: 34867719

SELECT num, diff, COUNT(1) `Count`
FROM ccc
GROUP BY num, diff

Open in new window

0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:phillyrob817
ID: 34867907
Thx guys for the reply. Thomasian I am not quite following, sorry, the COUNT(1)  'count' part i don't understand how to fit it in . I am not clear on the syntax. What does the count(1) 'count' represent? Can you clarify?
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 34867949
Ok count function counts all records where the parameter is not null. So by supplying a scalar value "1", the function counts all the records.

i.e. COUNT(1) = COUNT(*)
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 34867964
The `Count` (note it is a tick not a apostrophe) is the alias of the field. So you will be able to get the value returned by the count function with the field name "Count".
0
 
LVL 11

Expert Comment

by:Amar Bardoliwala
ID: 34868449
Hello phillyrob817,

you can achieve your result using "group by" in your query

change your query as following

 "select num, diff, count(diff) as mycount from ccc where  num = $b group by num,diff"

or for whole table use following

 "select num, diff, count(diff) as mycount from ccc  group by num,diff"

Above should solve your problem.

Thanks.
0
 

Author Closing Comment

by:phillyrob817
ID: 34899222
Thx to all that responded I used  Thomasian's response which works thx.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question