Number of Records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Number of Records

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-07 : 08:15:28

Hello,

I have a table which includes two fields:
[DocumentId] and [DocumentViewDate].

I need to do 2 different things which are not related:
1. Return the total number of records where DocumentId = @DocumentId
2. Return the total number of records where DocumentId = @DocumentId but organized by month (or any other timeframe) according to DocumentViewDate.

How can I do this?

Thanks,
Miguel

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-07 : 08:19:30

Select count(*) as [no_of_records] from table where DocumentId = @DocumentId

Select Datename(month, DocumentViewDate) as [Month], count(*) as [no_of_records] from table where DocumentId = @DocumentId 
Group by Datename(month, DocumentViewDate)
Order by 1

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-07 : 08:19:47


declare @no_of_rec int
select @no_of_rec = count(*)
from   yourtable
where  DocumentId = @DocumentId


select month(DocumentViewDate), no_of_rec = count(*)
from   yourtable
where  DocumentId = @DocumentId
group by month(DocumentViewDate)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-07 : 08:20:27

by 17 secs

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-07 : 08:24:49

On solution 2 is it possible to pass a parameter named TimeFrame which could be:
Hourly, Daily, Monthly, Yearly and then return the total number of records.

And then return the number of records according to the time frame?

Thanks,
Miguel

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-07 : 08:29:04

[code]
declare @TimeFrame varchar(10)

select case @TimeFrame
when 'day' then day(DocumentViewDate)
when 'month' then month(DocumentViewDate)
end,
no_of_rec = count(*)
from yourtable
where DocumentId = @DocumentId
group by case @TimeFrame
when 'day' then day(DocumentViewDate)
when 'month' then month(DocumentViewDate)
end
[/code]

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-07 : 10:29:51

Hi khtan,

I am getting an error with my code but I can't find the reason.
My stored procedure is as follow:

ALTER PROCEDURE [dbo].[DocumentsViews_GetDocumentViewsByTimeFrame]

-- Define the procedure parameters
@DocumentId UNIQUEIDENTIFIER,
@TimeFrame VARCHAR(10)

AS
BEGIN

-- Allows @@ROWCOUNT and the return of number of records when ExecuteNonQuery is used
SET NOCOUNT OFF

-- Group document views by timeframe
SELECT CASE LOWER(@TimeFrame)
WHEN 'day' THEN DAY(DocumentViewDate)
WHEN 'month' THEN MONTH(DocumentViewDate)
WHEN 'year' THEN YEAR(DocumentViewDate)
END,
NumberOfRecords = COUNT(*)
FROM dbo.DocumentsViews
WHERE DocumentId = @DocumentId
GROUP BY CASE @TimeFrame
WHEN 'day' THEN DAY(DocumentViewDate)
WHEN 'month' THEN MONTH(DocumentViewDate)
WHEN 'year' THEN YEAR(DocumentViewDate)
END

END

I get the error:
Column 'dbo.DocumentsViews.DocumentViewDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

On code line "SELECT CASE LOWER(@TimeFrame)"

Any idea why?

Thanks,
Miguel

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-07 : 10:37:36

[code]
SELECT CASE LOWER(@TimeFrame)
WHEN 'day' THEN DAY(DocumentViewDate)
WHEN 'month' THEN MONTH(DocumentViewDate)
WHEN 'year' THEN YEAR(DocumentViewDate)
END,
NumberOfRecords = COUNT(*)
FROM dbo.DocumentsViews
WHERE DocumentId = @DocumentId
GROUP BY CASE LOWER(@TimeFrame)
WHEN 'day' THEN DAY(DocumentViewDate)
WHEN 'month' THEN MONTH(DocumentViewDate)
WHEN 'year' THEN YEAR(DocumentViewDate)
END
[/code]

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-07 : 11:02:06

Thank you very much for your help!

Miguel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources