HI ,
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.

searchengine table has the following structure :

s_id s_name
1 google.com
2 altavista.com
3 google.co.in
4 search.yahoo.com

and so on

keywordhits table has
created s_id hits keyword
2003-08-08 14:00:00 1 25 flowers
2003-08-08 15:00:00 1 23 rings
2003-08-08 16:00:00 2 28 roses
2003-08-10 14:00:00 3 68 greetings
2003-08-11 14:00:00 1 30 flowers

The above is just example data

I need my output to be like this :

greetings 68

flowers 55
rings 23

roses 28

I tried a sample query like this :

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

flowers 55
rings 23

roses 28

greetings 68

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
logic first.

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
created visits
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 ?

Thanks in advance.