Solved

Count  number found in database

Posted on 2011-02-10
8
335 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

626 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