getting count - 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 2000 Forums

SQL Server Administration (2000)

getting count

Author

Topic

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2003-02-22 : 06:05:58

hi,
I have the following select query:
select colA,ColB,ColC from Table_name where ColD=1

now I want the count of the number of records in the table which have colD=1 along with all the columsn listed above just like :

select count(*) ,colA,ColB,ColC from Table_name where ColD=1

but here I'll have to use group by and it does'nt give me the desired result.Is there any work around??

any help appreciated.
Regards,
Harshal.

Expect the UnExpected

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-22 : 09:37:11

Try:

select colA,
ColB,
ColC,
(SELECT COUNT(*) FROM Table_name where ColD = 1) as RecCount
from
Table_name
where ColD=1

SQL needs to make two "passes" through the table in order to return the information you need, so a subquery is needed.

Not sure if there's a performance gain/loss, but you could also try:

SELECT ColA,ColB,ColC,RecCount
FROM
Table_name
CROSS JOIN
(SELECT COUNT(*) as RecCount FROM Table_name where ColD = 1) B
where ColD=1

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources