March 9, 2012 at 7:13 am
Hello All,
I need some guidance with the best way to add a simple count to my select statement.
I don't know how to query and then get a count from the query.
I have a very simple select of four columns where the client is X, I need to get the records as well as a count of records as part of the query results. I am missing something very basic. Please help.
WITH DCOUNT as
(
SELECT COUNT(CLCDX) FROM CLDHIST as DCOUNT
WHERE CLCDX = 'UNI'
)
SELECT CLCDX as [Client], CDREFX as [DRef], CDCDX as [DCode], CBCDX as [BCode], DCOUNT as [Count]
FROM CLDHIST
WHERE CLCDX = 'UNI'
or do I create the count first?
March 9, 2012 at 8:20 am
No sample or test data so can't test so maybe something like...
SELECT *, COUNT(*) OVER (PARTITION BY [column_name]) AS prt FROM CLDHIST;
March 9, 2012 at 8:20 am
Looks like I got beaten to it 🙂
March 9, 2012 at 8:27 am
Okay, well thank you for your advise.
This was just an example, I put in the names for some clarity, I guess it didn't work.
I was really just interested in advise on the idea. Sorry for not posting in accordance with ISO standards.
March 9, 2012 at 9:07 am
sqlservant (3/9/2012)
Okay, well thank you for your advise.This was just an example, I put in the names for some clarity, I guess it didn't work.
I was really just interested in advise on the idea. Sorry for not posting in accordance with ISO standards.
Don't worry about it! AFAIK, Mr Celko is the only "ISO Cop" that walks these corridors. 😛
We do, however, typically need a test table and some data to test any responses we might give you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply