Need a more powerful query than this... 1

[hapax]

Here's the simple query I have so far:

SELECT H.*, G.GameName
FROM GamePageHits H, Games G
WHERE H.GameID = G.GameID

I need a more powerful query that does the following:

Returns the total number of hits for each GameID, during a specified range of dates, sorted DESC by game ID.

This is beyond my current skills. Much thanks.

 

[hapax]

More info:

My GamePageHits table has the following columns:
HitID, GameID, Time, UserID

I want to know how many hits was recieved by each GameID in GamePageHit, during a date range that I specify in the query.

 

[r937]

SELECT GameID
     , count(*) as Hits
  FROM GamePageHits 
 WHERE Time >=  @StartDateTime
   AND Time <=  @EndDateTime
GROUP 
    BY GameID

r937.com | rudy.ca

 

[hapax]

OK, I've got the following code so far. But it still has two problems:

1. The Time range doesn't work - returns nothing.
2. I want it to be sorted by Hits (the most hits at the top of the list).

SELECT GameID,
count(*) as Hits,
(Select GameName from Games where GamePageHits.GameID = Games.GameID ) as GameName
FROM GamePageHits
WHERE Time >= 7-15-2007
AND Time <= 7-16-2007
GROUP BY GameID

 

[r937]

7-15-2007 is not a date, it is an arithmetic expression equalling -2015

try like this:

SELECT GameName 
     , Hits
  FROM Games 
INNER
  JOIN (
       SELECT GameID
            , count(*) as Hits
         FROM GamePageHits 
        WHERE Time >= '2007-07-15'
          AND Time <= '2007-07-16'
       GROUP 
           BY GameID
       ) as H
    ON H.GameID = Games.GameID
ORDER
    BY Hits DESC

r937.com | rudy.ca

 

[hapax]

Excellent. Thanks!

 

[PHV]

Why not simply this ?
SELECT G.GameName, COUNT(*) Hits
FROM Games G INNER JOIN GamePageHits H ON G.GameID = H.GameID
WHERE H.Time BETWEEN '2007-07-15' AND '2007-07-16'
GROUP BY G.GameName
ORDER BY 2 DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886