Solved

Count  number found in database

Posted on 2011-02-10
8
328 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
xampp tool 12 28
Mysql sync between 3-4 mysql db 4 25
phpmailer in WHILE loop - weird results 10 28
What is the best PDF generator to use? 1 30
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
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…
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.

895 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now