Not grouping by date?

Question

I have a database table that stores document information (DocID, Name, Type, Owner, etc). Then I have another table that stores the times the document has been downloaded (DocID, DownloadTime, etc). I'm trying to set up a page for recording the number of hits that different kinds of documents (policies, forms, publications, etc) get in a specific month and year (I have a dropdown for each that the user selects).

I also want to be able to leave Month or Year empty, so the user can get hits for all months in a year, for example. The query below works ok when the user selects the month and year. However, when month or year is left empty the gridview doesn't show the document types grouped. For example if I want to see hits for January 2010, I get:

Policies : 210 hits
Forms: 98 hits

etc

But when the users chooses all months in 2010, the user gets:

Policies: 210 hits
Policies: 87 hits
Policies: 23 hits
Policies: 87 hits
Forms 

etc

So I'm getting all months in different rows but not grouped as one kind. Where is the query wrong?

SELECT COUNT(Counter.DocID) AS Counter,
       Doc.DocType,
       MONTH(Counter.DownloadDate) AS MonthDownload,
       YEAR(Counter.DownloadDate) AS YearDownload
  FROM Counter
       INNER JOIN Doc ON Counter.DocID = Doc.DocID
 GROUP BY Doc.DocType,
       MONTH(Counter.DownloadDate),
       YEAR(Counter.DownloadDate)
HAVING (MONTH(Counter.DownloadDate) = @Month1 OR @Month1 IS NULL) AND
       (YEAR(Counter.DownloadDate) = @Year1 OR @Year1 IS NULL)

Answer 1

The issue is in your GROUP BY clause.

If you say GROUP BY DocType, Month, Year that means that any time any of those is different from the others, count them separately.

What you need to do is GROUP BY year only if you want the total for the whole year, and group by month, year if you want the count for each month of each year.

Answer 2

When you want the annual aggregate, you have to use a different query that does not group by the months - or you have to post-process the results to add up the counts from the different months within the year.

As written, your code always groups by months and years, generating new rows when the month changes - it can do no other without breaking every rule (ok, I exaggerate - a lot of rules) in SQL.

Answer 3

Remove the Month related grouping from the query to get one aggregated records per document type in the given year.

SELECT COUNT(Counter.DocID) AS Counter,
       Doc.DocType,
       YEAR(Counter.DownloadDate) AS YearDownload
  FROM Counter
       INNER JOIN Doc ON Counter.DocID = Doc.DocID
 GROUP BY Doc.DocType,
       YEAR(Counter.DownloadDate)
HAVING 
       (YEAR(Counter.DownloadDate) = @Year1 OR @Year1 IS NULL)