I need help in solving two queries.
1)First query :
I need to display the "top most keywords from search engines" in my website during
a certain period.
For this I have two tables called searchengines and keywordhits.
select sum(keywordhits.hits) as total from searchengine left join
keywordhits on searchengine.s_id=keywordhits.s_id
group by keywordhits.s_id , keywordhits.keyword
order by searchengine.s_id , total desc
But the result was not what I expected,it was like
This is because, it orders the search engine name first and then the
hits.If I take of the order by clause , then the group by is not working properly.Can anyone help ?
2)This is still more complicated (atleast to me) and I would tell the
The table involved is only one in this case and it is "visits"
table.I am calculating "the number of visitors coming to my website"
during a period.In this , I need to show the results like
June Visits Historical Weighted Average
Monday 1,2003 87 45
Tuesday 2,2003 75 76
and so on,
The historical weighted average need to be calculated as follows:
(Historical average for Monday 1,2003)
I need to show the average visits to my website on all
mondays since the installation of my website.In that , the last
3 months data would have a weightage of 2 and the others 1.
The table involved has the following data structure
2003-07-01 13:00:00 40
2003-07-01 13:30:00 47
2003-07-02 14:00:00 20
2003-07-02 14:30:00 21
2003-07-02 15:00:00 20
and so on.
I don't know whether this is possible by a single query but
am not able to query the number of weekdays itself.
I gave like :
select date_format(created,'%Y-%m-%d') as c ,sum(visits),
date_format(created,'%w') as w1,
count(date_format(created,'%w')) as w
from visits group by w1
It is summing up the visits properly and is also displaying the
week number in w1. But is not counting the number of weeks elapsed
in w (This is because I have 48 records per day.Sometimes
I may not have 48 also.).Can anyone tell me how
to query the number of week days elapsed ( like Mondays : 5,Tuesdays, 3) etc ?