Add Count Function to my query

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

General SQL Server Forums

New to SQL Server Programming

Add Count Function to my query

Author

Topic

kathyc2003
Starting Member

15 Posts

Posted - 2007-12-17 : 13:30:58

Hello,

I have created the following query... and need to get
the total records display for my report. I have tried
adding in the count(*) function to my select list, but I get
errors. Any help is appreciated.

SELECT
A.ParentSubjectName,
A.ParentSubject,
A.SubjectId,
B.CreatedOn

FROM dbo.Subject A
INNER JOIN dbo.Incident B ON A.SubjectId = B.SubjectId

WHERE A.ParentSubjectName LIKE 'ACDelco Products%'

AND (B.CreatedOn >= '2007-01-01' AND B.CreatedOn <= '2007-11-30')

AND A.SubjectId IN
(
'C44ADE3E-527B-DC11-8A2D-00170857BDE7',
'F8758E52-527B-DC11-8A2D-00170857BDE7',
'7E65F458-527B-DC11-8A2D-00170857BDE7',
'7F65F458-527B-DC11-8A2D-00170857BDE7',
'2BE35262-527B-DC11-8A2D-00170857BDE7',
'2AE35262-527B-DC11-8A2D-00170857BDE7',
'A2002127-527B-DC11-8A2D-00170857BDE7',
'41A8A66F-527B-DC11-8A2D-00170857BDE7',
'A3002127-527B-DC11-8A2D-00170857BDE7',
'D6C08B45-527B-DC11-8A2D-00170857BDE7',
'C439FB4B-527B-DC11-8A2D-00170857BDE7'
)

ORDER BY B.CreatedOn[/blue]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-17 : 13:44:31

What application runs "your report"? You can count the rows in the report writer. Somehow I suspect "your report" is the sql output directly. If that is the case, one method is to: "select @@rowcount as [total rowcount]" imediately after your select statement.

Be One with the Optimizer
TG

kathyc2003
Starting Member

15 Posts

Posted - 2007-12-17 : 16:14:09

Hi

Thanks for answering, I am using SRS Reports.
I need to display the name of the category once
(example PRODUCT_A) then based on a date range
display only the total_count of the number
of rows returned in the resultset.

I have tried creating a count(PRODUCT_A) and
displaying that in the body section and am
getting an error.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 16:15:28

Try count(DISTINCT PRODUCT_A)

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources