Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Count  number found in database

Posted on 2011-02-10
8
Medium Priority
?
337 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 500 total points
ID: 34867719

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

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

916 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