COUNT(*) within a SELECT - 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 2008 Forums

Transact-SQL (2008)

COUNT(*) within a SELECT

Author

Topic

macca
Posting Yak Master

146 Posts

Posted - 2011-10-26 : 09:33:10

I want to get a COUNT(*) of the number of records returned in this query:

SELECT a.Id, a.Forename, a.Surname, b.Location, a.No, a.date
FROM main As a
INNER JOIN local As b ON local.Loc_ID = main.loc_id
WHERE (a.date BETWEEN @FromDate AND @ToDate)

Problem is when I put in a Count(*) it throws an error.
Anyone any ideas?
Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-26 : 09:36:10

[code]
SELECT a.Id, a.Forename, a.Surname, b.Location, a.No, a.date, count(*) over()
FROM main As a
INNER JOIN local As b ON local.Loc_ID = main.loc_id
WHERE (a.date BETWEEN @FromDate AND @ToDate)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

macca
Posting Yak Master

146 Posts

Posted - 2011-10-26 : 09:39:50

I tried that and it still throws an error saying "There is an error in the query".
Any other ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-26 : 09:41:18

what is the exact error message ?
Are you using SQL Server 2008 ?

KH
[spoiler]Time is always against us[/spoiler]

macca
Posting Yak Master

146 Posts

Posted - 2011-10-26 : 09:52:34

I am using SSRS SQL 2008.
The Error message is happening when I am utting in the query for the SSRS Report.
The Error message is "There is an error in the query".

Any ideas?

macca
Posting Yak Master

146 Posts

Posted - 2011-10-26 : 09:57:40

Thanks khtan that just worked for me.
I was doing something else wrong there.

Many thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources