CODEWITHSUNDEEP
.net
DigitalMind2014
DigitalMind2014

Reputation: 3

Number of records in database between two dates

I have the following query in .net application

SELECT
  COUNT(Counter.DocuID) AS CountDownload,
  Counter.DocuID,
  CONVERT(CHAR(4), Counter.DownloadDate, 112),
  Table.DocName
FROM Counter
INNER JOIN Table ON Counter.DocuID = Table.DocID
WHERE (Counter.DocuID = @DocID)
  AND (Counter.DownloadDate >= @startDate)
  AND (Counter.DownloadDate < DATEADD(DAY, 1, @endDate))
  AND (Table.DocType <> @DocType)
GROUP BY
  Counter.DocuID,
  CONVERT(CHAR(4), Counter.DownloadDate, 112),
  Table.DocName,
  Table.DocType

I need this query to provide the following output

DocID: 1
DocName : NameofDoc
CountofDownloads : 123

In which CountofDownloads is total number of rows between start and end dates.

Instead I get CountofDownloads split by year if date range is in two different years:

DocID: 1
DocName : NameofDoc
CountofDownloads : 100

DocID: 1
DocName : NameofDoc
CountofDownloads : 23

Can I get a grand total without splitting by year?

Upvotes: 0

Views: 26

Answers (1)

Bohemian
Bohemian

Reputation: 425238

Wrap your query in a group by:

select
  DocID,
  DocName,
  sum(CountofDownloads) as CountofDownloads
from (
    -- your query here
) x
group by DocID, DocName

Upvotes: 1

Related Questions