AVG between two dates

[lildiapaz]

Hi,

I am trying to figure out how to find the avg in between two dates in the work_date field. For example, let's say I want the avg from 2007-01-24 to 2007-05-06, I would have to find the avg by taking the values in the daily_typing_pages columns and add up all the columns between the the dates provided. (E.G. (100+220+350+250+170)/5).

+------+------+------------+--------------------+
| id   | name | work_date  | daily_typing_pages |
+------+------+------------+--------------------+
|    1 | John | 2007-01-24 |                250 |
|    2 | Ram  | 2007-05-27 |                220 |
|    3 | Jack | 2007-05-06 |                170 |
|    3 | Jack | 2007-04-06 |                100 |
|    4 | Jill | 2007-04-06 |                220 |
|    5 | Zara | 2007-06-06 |                300 |
|    5 | Zara | 2007-02-06 |                350 |
+------+------+------------+--------------------+
 

this is what i tried but it returns empty set.

select name, avg(daily_typing_pages) 
from product 
where work_date between '2007-01-24' and '2007-05-06' 
group by name
 

This is really important any help is appreciated

[Atli]

Hi.

I tested this over on my server and it worked just fine.
This is what I did:

CREATE TABLE t1 (
  name VarChar(255) Not Null,
  work_date DateTime Not Null,
  daily_typing_pages int Not Null
);
 
INSERT INTO t1 (name, work_date, daily_typing_pages)
VALUES
('John', '2007-01-24',  250),
('Ram ', '2007-05-27',  220),
('Jack', '2007-05-06',  170),
('Jack', '2007-04-06',  100),
('Jill', '2007-04-06',  220),
('Zara', '2007-06-06',  300),
('Zara', '2007-02-06',  350);
 
SELECT name, AVG(daily_typing_pages) 
FROM t1 
WHERE work_date BETWEEN '2007-01-24' AND '2007-05-06' 
GROUP BY name
 

And I got these results:

+------+-------------------------+
| name | AVG(daily_typing_pages) |
+------+-------------------------+
| Jack |                135.0000 |
| Jill |                220.0000 |
| John |                250.0000 |
| Zara |                350.0000 |
+------+-------------------------+
4 rows in set (0.00 sec)

Doesn't seem to be a problem with your query.

P.S.
Please use [code] tags when posting your code examples.
(See How to ask a question)

[code] ... Code goes here... [/code]

Thank you.
Moderator

[lildiapaz]

Thanks for the quick response,

Well its still not working for me, The field with the dates are of date type, do you think that's the problem? If so, how do I resolve the issue and return the query you have above?

Thanks,

[Atli]

Whether it's a Date or DateTime field should make no difference at all. The date part of the data would still be the same.

The query itself works, so the problem must be somewhere else.
We need more info if we are to be able to help you with this.

Could you post the CREATE TABLE statement you used to create your table?
(You can get from MySQL by doing SHOW CREATE TABLE)

What version of MySQL are you running?
Are you executing the query through some API?

Anything else that might be having an effect on this?

[lildiapaz]

I am using MYSQL version 5.0 and I am connecting to a table that was already created with the appropriate fields, and I am using JDBC to connect to the instance and tables which connects and works fine. The only problem is when I try and run the query is where I don't get any results.

  SELECT name, AVG(daily_typing_pages) 
FROM t1 
 WHERE work_date BETWEEN '2007-01-24' AND '2007-05-06' 
GROUP BY name 

Thanks

[lildiapaz]

Also here is code snippet of jdbc prog

String url = "jdbc:mysql://MyDriver/";
        String dbName="product";
        String driver="com.mysql.jdbc.Driver";
        String username="root";
        String password="password";
        String myQuery="SELECT name, AVG(daily_typing_pages) 
 FROM t1 
  WHERE work_date BETWEEN '2007-01-24' AND '2007-05-06' 
 GROUP BY name ";
 
 
 
        try
        {
            Class.forName(driver).newInstance();
            connect =   DriverManager.getConnection(url+dbName,username,password);
            Statement state=connect.createStatement();        
 
              ResultSet result=state.executeQuery(myQuery);
                while (result.next())
                {                
                //do something
}
 

Thanks,