# Thread: Average type query

1. Registered User
Join Date
Aug 2010
Posts
10

## Unanswered: Average type query

Hello peoples i got a question . I havent been working alot with SQL queries and the like so please bear with me cause im slowly learning.

Anyhow i got a table that looks like this
date(date), time(TIME), name(varchar15), activity (int4).

Just to be clear 'activity' is the time a person spent on an activity in minutes.

The thing i want to achieven in a query is the following formula:
Activity = (1xaverage activity +3xaverage activity from yesterday + 2x average activity the day before yesterday) /4.6

average activity is calculated by the following:
Average activity = (MAX(activity) + MIN(activity)) /2, I have reasons to calculate it like this .

Anyhow im a bit stuck on how to do this. The parts i got so far is to calculate the average activity per day in a seperate query like this:
SELECT date, (MAX(activity) + MIN(activity)) /2 as avActivity from tabel group by DAY(date);

however i really dont know how to proceed further, as said eariler im a bit stuck. So if anyone has a good idea on how to go further thansk in advance

2. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
you'll need two self-joins, one to get yesterday and one for the day before yesterday

what happens if these don't exist? what is the formula if yesterday or the day before yesterday is missing?

3. Registered User
Join Date
Aug 2010
Posts
10
Originally Posted by r937
you'll need two self-joins, one to get yesterday and one for the day before yesterday

what happens if these don't exist? what is the formula if yesterday or the day before yesterday is missing?
Ah joins, havent been that far yet in the learning proces, ill look em up now thanks alot .

Ah and btw yesterday and the day before yesterday will ALWAYS exist. Mainly the query is being run from a php form where u can select from a dropdown the date. I already made it that the 'lowest date' that is selectable is the 3rd one in the table so that there is always a day before yesterday, and a day before

4. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
assume \$dateparameter as a value like '2010-08-10'
Code:
```SELECT \$dateparameter AS thedate
, (   today.avActivity
yesterday.avActivity * 3 +
daybefore.avActivity * 2 ) / 4.6    AS Activity
FROM ( SELECT (MAX(activity) + MIN(activity)) / 2 AS avActivity
FROM daTable
WHERE `date` = \$dateparameter ) AS today
CROSS
JOIN ( SELECT (MAX(activity) + MIN(activity)) / 2 AS avActivity
FROM daTable
WHERE `date` = \$dateparameter - INTERVAL 1 DAY ) AS yesterday
CROSS
JOIN ( SELECT (MAX(activity) + MIN(activity)) / 2 AS avActivity
FROM daTable
WHERE `date` = \$dateparameter - INTERVAL 2 DAY ) AS daybefore```

5. Registered User
Join Date
Aug 2010
Posts
10
Thanks alot sorry for late reply but ive been trying out to figure out the errors the query gave myself. Yours worked perfectly with the 1 exception :P

WHERE `date` = \$dateparameter - INTERVAL 2 DAY ) AS daybefore
If i put the quotes around 'date' it doesnt work. Took me some time to figure out to remove em. After i did that it worked perfectly.

Thanks alot already. Now im trying to figure out how to do it for mulitple days but no succes in that yet :P. With multiple days. I could just run a loop in PHP but trying to do it in mysql :P
Last edited by bleastan; 08-12-10 at 09:58.

6. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
Originally Posted by bleastan
WHERE `date` = \$dateparameter - INTERVAL 2 DAY ) AS daybefore
If i put the quotes around 'date' it doesnt work.
do you understand the syntactic difference between quotes and backticks?

7. Registered User
Join Date
Aug 2010
Posts
10
Originally Posted by r937
do you understand the syntactic difference between quotes and backticks?
to be honest i cant say i do (or did). Just looked it up and apparently back ticks should work, but i kept getting errors and when i removed em it seemed to work :P

8. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
assuming the column name is date,

WHERE `date` = ... will always work

WHERE 'date' = ... will never work

WHERE date = ... might work, but since DATE is a reserved word, might not

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•