Help with AVG()

------=_NextPart_000_00AB_01CC29FE.EC4700D0
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable


Hi Everyone

I am trying to figure out how to write a SELECT query that will give me =
the average of `bible_anagrams`.`views` starting on the date specified =
in `bible_anagrams_rss_feed`.`rss_feed_date` and the previous 6 calendar =
days (for a total of 7 days). What I am trying to figure out is the =
average of how many times the anagrams RSS Feed were accessed between =
June 1st and 7th, June 2nd and 8th, June 3rd and 9th, etc.

- There is 1 row for each date in the table bible_anagrams_rss_feed.
I tried the following syntax, but it is giving me an overall average, =
not by the date ranges:



SELECT AVG(`bible_anagrams`.`views`) AS average_views FROM =
`bible_anagrams` INNER JOIN `bible_anagrams_rss_feed` ON =
`bible_anagrams`.`reference` =3D =
`bible_anagrams_rss_feed`.`bible_anagrams_reference` ORDER BY =
`bible_anagrams_rss_feed`.`rss_feed_date` DESC LIMIT 7




I am wanting the query I am asking help for to be included as one of the =
mySQL results, where I have indicated â€=9CAVERAGE VIEWS QUERY =
HEREâ€=9D (although if there is a better way I am opening to =
learning it):



SELECT `bible_anagrams_rss_feed`.`rss_feed_date` , =
`bible_anagrams`.`reference`, `bible_anagrams`.`bible_anagram_word` , =
`bible_anagrams`.`views` ,=20

(

AVERAGE VIEWS QUERY HERE

) AS average_views

FROM `bible_anagrams_rss_feed` INNER JOIN `bible_anagrams` ON =
`bible_anagrams`.`reference` =3D =
`bible_anagrams_rss_feed`.`bible_anagrams_reference`=20
ORDER BY `bible_anagrams_rss_feed`.`rss_feed_date` DESC



Thanks for helping me,

Ron

The Verse of the Day
â€=9CEncouragement from Godâ€=99s Wordâ€=9D
http://www.TheVerseOfTheDay.info =20

------=_NextPart_000_00AB_01CC29FE.EC4700D0--

Re: Help with AVG()

you have no WHERE clause in your query.
""Ron Piggott"" wrote in message
news:F6C4EF096B4446CA8AECD201C1E86C39@RonPiggottPC...

Hi Everyone

I am trying to figure out how to write a SELECT query that will give me the
average of `bible_anagrams`.`views` starting on the date specified in
`bible_anagrams_rss_feed`.`rss_feed_date` and the previous 6 calendar days
(for a total of 7 days). What I am trying to figure out is the average of
how many times the anagrams RSS Feed were accessed between June 1st and 7th,
June 2nd and 8th, June 3rd and 9th, etc.

- There is 1 row for each date in the table bible_anagrams_rss_feed.
I tried the following syntax, but it is giving me an overall average, not by
the date ranges:



SELECT AVG(`bible_anagrams`.`views`) AS average_views FROM `bible_anagrams`
INNER JOIN `bible_anagrams_rss_feed` ON `bible_anagrams`.`reference` =
`bible_anagrams_rss_feed`.`bible_anagrams_reference` ORDER BY
`bible_anagrams_rss_feed`.`rss_feed_date` DESC LIMIT 7




I am wanting the query I am asking help for to be included as one of the
mySQL results, where I have indicated "AVERAGE VIEWS QUERY HERE" (although
if there is a better way I am opening to learning it):



SELECT `bible_anagrams_rss_feed`.`rss_feed_date` ,
`bible_anagrams`.`reference`, `bible_anagrams`.`bible_anagram_word` ,
`bible_anagrams`.`views` ,

(

AVERAGE VIEWS QUERY HERE

) AS average_views

FROM `bible_anagrams_rss_feed` INNER JOIN `bible_anagrams` ON
`bible_anagrams`.`reference` =
`bible_anagrams_rss_feed`.`bible_anagrams_reference`
ORDER BY `bible_anagrams_rss_feed`.`rss_feed_date` DESC



Thanks for helping me,

Ron

The Verse of the Day
"Encouragement from God's Word"
http://www.TheVerseOfTheDay.info



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Help with AVG()

------=_NextPart_000_000C_01CC2A43.D61222F0
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable


I have continued to work on the average query I need help. I am still =
needing help.

What I donâ€=99t understand is why=20

`bible_anagrams_rss_feed`.`rss_feed_date` BETWEEN =
`bible_anagrams_rss_feed`.`rss_feed_date` AND DATE_SUB( =
`bible_anagrams_rss_feed`.`rss_feed_date` , INTERVAL -1 WEEK )

wonâ€=99t isnâ€=99t selecting the 7 consecutive dates leading up =
to and including the date of `bible_anagrams_rss_feed`.`rss_feed_date` =
I am only getting the value of `bible_anagrams`.`views` from the current =
date as the value of average_views in the query:


SELECT=20

`bible_anagrams_rss_feed`.`rss_feed_date` , SUM( =
`bible_anagrams`.`views` ) AS total_views , SUM( =
`bible_anagrams`.`views` ) / =
COUNT(`bible_anagrams_rss_feed`.`reference`) AS average_views

FROM `bible_anagrams_rss_feed` INNER JOIN `bible_anagrams` ON =
`bible_anagrams_rss_feed`.`bible_anagrams_reference` =3D =
`bible_anagrams`.`reference`

WHERE=20

`bible_anagrams_rss_feed`.`rss_feed_date` BETWEEN =
`bible_anagrams_rss_feed`.`rss_feed_date` AND DATE_SUB( =
`bible_anagrams_rss_feed`.`rss_feed_date` , INTERVAL -1 WEEK )

GROUP BY `bible_anagrams_rss_feed`.`rss_feed_date`

ORDER BY `bible_anagrams_rss_feed`.`rss_feed_date` DESC
rss_feed_date Descending total_views average_views
2011-06-13 12:00:02 13 13.0000
2011-06-12 12:00:01 10 10.0000
2011-06-11 12:00:02 18 18.0000
2011-06-10 12:00:02 14 14.0000
2011-06-09 12:00:01 20 20.0000
2011-06-08 12:00:01 28 28.0000
2011-06-07 12:00:03 15 15.0000
2011-06-06 12:00:02 21 21.0000
2011-06-05 12:00:01 20 20.0000
2011-06-04 12:00:02 33 33.0000
2011-06-03 12:00:02 23 23.0000
2011-06-02 12:00:02 25 25.0000
2011-06-01 12:00:01 13 13.0000
2011-05-31 12:00:02 41 41.0000
2011-05-30 12:00:01 27 27.0000
Ron
The Verse of the Day
â€=9CEncouragement from Godâ€=99s Wordâ€=9D
http://www.TheVerseOfTheDay.info =20

------=_NextPart_000_000C_01CC2A43.D61222F0--

Re: Help with AVG()

Why not try setting vars to the date values you wish to select and use them
in your where clause? You may have something wrong in this complex syntax.
Something like:

$st_date = today() ??
$end_date = $st_date less 7 days ??

Where rss_feed_date >= $st_date and rss_feed_date <= $end_date

btw - why not use table aliases to save on typing and improve readability?

""Ron Piggott"" wrote in message
news:82DDACD57AB849159889CB7C6A3FFAAB@RonPiggottPC...
`bible_anagrams_rss_feed`.`rss_feed_date` BETWEEN
`bible_anagrams_rss_feed`.`rss_feed_date` AND DATE_SUB(
`bible_anagrams_rss_feed`.`rss_feed_date` , INTERVAL -1 WEEK )




--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php