Solved

Count  number found in database

Posted on 2011-02-10
8
331 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

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
category table 2 24
php string detection problem 7 33
000webhost.com default error log 1 24
mysql to shows result of all other id in another colunm if id select in table 3 25
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and 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.
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 …

813 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

18 Experts available now in Live!

Get 1:1 Help Now