1

I have a problem using mySQL with my PHP admin and have a problem querying my DB This is the query statement:

SELECT Record_ID, Datalog_ID, TIME, AVG( Value ) 
FROM  `Datalog_Values` 
WHERE Datalog_ID =  '241'
AND TIME
BETWEEN  '2012-06-01'
AND  '2012-06-30'
GROUP BY HOUR( TIME )

The result i am getting is only the results from the first day and not the period.i.e. the month.

what is wrong with my statement????

Improve this question
2
  • 2
    Please post sample data and desired output. Commented Nov 8, 2012 at 16:05
  • can you post (a sample of) your data? Commented Nov 8, 2012 at 16:06

3 Answers 3

Reset to default
1

Your query suffers from using a (mis)feature of MySQL called hidden columns. The query is choosing arbitrary values for Record_id, Datalog_id, and Time, because these are not mentioned in the group by clause.

If I speculate that the intended query is:

SELECT Record_ID, Datalog_ID, hour(TIME), AVG( Value ) 
FROM  `Datalog_Values` 
WHERE Datalog_ID =  '241' AND TIME BETWEEN  '2012-06-01' AND '2012-06-30'
GROUP BY Record_id, Datalog_id, hour(TimE )

Then this starts to make sense, assuming that the goal of the query is to produce results for each Record_Id and hour of the day for the given DataLog_id. Do you want results for each hour of the day or each hour of the month?

If you provide sample data and explain the output you want, then your question can be better answered.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Unfortunately I cant provide a sample, but I will try clear up what the problem is. The results I am getting are average values for the first date only ie. 2012-06-01 and not for every hour for the entire month. I will try the query you posted above and see if it helps. thanks.
How are you determining that the values are only from the first day? The "TIME" variable in the output is probably going to be the first time encountered, so it will always be from the first day. I'd add in a count(*) to see how many records are included in each row.
0

You need to compare using the same datatype. So if your field is date/time YYYY-MM-DD HH:MM:SS then you need to do BETWEEN using the same format. Try just putting 00:00:00 at the end of both your dates.

Improve this answer

3 Comments

I have tried both those methods, but when I group by month i simply just get an average for the entire month i think? and when I group by time, I simply get results for the entire month. What I would like is an hourly average for the month? Is there another method for completing this? Should I try off my Workbench?
So you want a monthly average of the hourly average?
If this is the case, you could create an aliased table which gives you the hourly averages for one month and use it to get the average of those numbers. Sorry if my answer is vague, I'm trying to get a grasp of what you need. Maybe provide a sample data table and a sample desired output?
0

IF your Time column has the format that you give in your where condintions ('YYYY-MM-DD') then the Group By HOUR (TIME) doesn't make sense. Try doing a GROUP BY TIME or GROUP BY MONTH (TIME)

otherwise you 've got your where conditions the wrong way and you need to add hh:mm:ss in the end of each one.

If you want an hourly average then make sure your Time column's datatype is Date/Time Which means that its format is ('yyyy/MM/dd hh:mm:ss')

If you have this format then I think this SO question - answer will help you out: SQL Server Group by Count of DateTime Per Hour?

Of course its for SQL Sever but you will get the concept. You can see there how you can group by hours of each day. Cause if you just Group By Hour(Time) you will get groupings for specific hours of the WHOLE month not each day seperately.

Improve this answer

1 Comment

I Updated my answer. Check it out

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.