Problem defining a SELECT that does what I need... 1

[BabyJeffy]

Good morning all,

I've been working with mySQL (running with Tomcat/Apache on Windows 2003 Server) on our intranet for about 3 months now. I started this role with no hands-on mySQL and have managed to keep my head above water for most of that time (no support in-house).

I'm working on a page for the intranet here that is intended to display the frequency of certain types of records in the database (approximately 6000 records in a record set at once).

I have a table defined (and populated) as:

create table myTable (
page_id int(5),
comment varchar(10),
time_stamp timestamp(10)
);

I'm trying to build a select statement that will show how many records are in the recordset for each page_id (that occurs within a time period marked by the time_stamp field).

Here is a sample recordset returned from "SELECT page_id from myTable":

+---------+
| page_id |
+---------+
|       1 |
|       2 |
|       3 |
|       2 |
|       2 |
|       2 |
|       1 |
|       1 |
+---------+

Here is what I would like to be able to produce (sorted on the "Number of records" column)

Page_id    Number of records
      2    4
      1    3
      3    1

I've tried combinations of DISTINCT and COUNT (no headway at this stage) -- but whilst I can understand the logic enough to describe the problem, I am not able to figure the SQL to solve it (mostly due to my current limited understanding of the syntax available to solve the problem).

I'd appreciate any help on an appropriate select statement that you can come up with.

Cheers,
Jeff

 

[r937]

select Page_id
     , count(*) as NumberOfRecords
  from yourtable
 where time_stamp between x and y
group
    by Page_id

rudy

http://r937.com/

SQL Consulting

 

[BabyJeffy]

r937...

That's doing exactly what I needed. I'm just off to check up on what "group by" actually means for this query (more new syntax for me) -- but this is giving me exactly what I needed.

Thanks,
Jeff