Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

BabyJeffy

Programmer
Sep 10, 2003
4,189
GB
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:

Code:
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":
Code:
+---------+
| 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)

Code:
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
 
Code:
select Page_id
     , count(*) as NumberOfRecords
  from yourtable
 where time_stamp between x and y
group
    by Page_id

rudy
SQL Consulting
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top