[RESOLVED] Simple SQL Count Question

[conwa02976]

I am using SQL Server 2005 but for this question I know that is not a big deal.

This query gets me my desired results:
SELECT DISTINCT SerialNum, CommentNum
FROM CSSComments WHERE BatchID = 'Test'

It returns four records which is correct.

I just need a count showing that I have 4 records.

I am trying this but I get a syntax error:
SELECT Count(*) as Count FROM
(SELECT DISTINCT SerialNum, CommentNum
FROM CSSComments WHERE BatchID = 'Test')

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.

How do I get a return value of 4.

Thanks

[GaryMazzone]

Try
Select SerialNum, CommentNum,count(*)
From CSSComments
WHERE BatchID = 'Test'
GROUP BY SerialNum, CommentNum

[Minolwen]

Untested, but try:

SELECT COUNT(DISTINCT SerialNum, CommentNum) as "Count" FROM CSSComments WHERE BatchID = 'Test'

[conwa02976]

That basically gives me the exact same results as my SQL except that you added the count column.

What I need is an ExecuteScalar just returning the number 4 showing that I have 4 records.

Thanks Gary

[conwa02976]

Minolwen,

Got this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

Thanks

[techgnome]

Only don't use "Count" .... the quotes will cause a problem.... instead give it a name (other than count) ...

SELECT COUNT(DISTINCT SerialNum, CommentNum) as RecordCount FROM CSSComments ...


-tg

[conwa02976]

techgnome

same error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

Thanks

[techgnome]

Actually, I don't think your original query was all that far off...

SELECT Count(*) as RecordCount FROM
(SELECT DISTINCT SerialNum, CommentNum
FROM CSSComments WHERE BatchID = 'Test')

OR

SELECT COUNT(DISTINCT SerialNum + CommentNum) as RecordCount FROM CSSComments

either of those should work...

-tg

[conwa02976]

Success!!

techgnome, the first query still didn't work. It gave me the same error that I was getting in my original post about an invalid ")".

The second query works like a charm.

Thanks for everyones input, will mark as Resolved!!!