Having Clause - 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

General SQL Server Forums

New to SQL Server Programming

Having Clause

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-09-25 : 11:02:42

I need some help. I am trying to use the having clause to pull a count. For some reason it is only showing me a 1 count. I know there are other pcsp_sys that have a count of 8 or so. Can you help me and tell me what I am doing wrong?

Select Distinct
pcsa_id1,PCS_TAX1,PCSP_SYS,pcsa_lname,pcsa_fname,pcsa_minit,pcsa_degre,pcsa_addr1,pcsa_addr2,
pcsa_city,pcsa_state,PCSA_ZIP,County,val_desc, count(*) as 'COUNT'

from vw_Unique_DelCred_FINAL

GROUP BY pcsa_id1,PCS_TAX1,PCSP_SYS,pcsa_lname,pcsa_fname,pcsa_minit,pcsa_degre,pcsa_addr1,pcsa_addr2,
pcsa_city,pcsa_state,PCSA_ZIP,County,val_desc

HAVING COUNT(PCSP_SYS) <= '25'
ORDER BY count desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 11:05:37

quote:
Originally posted by werhardt

I need some help. I am trying to use the having clause to pull a count. For some reason it is only showing me a 1 count. I know there are other pcsp_sys that have a count of 8 or so. Can you help me and tell me what I am doing wrong?

Select Distinct
pcsa_id1,PCS_TAX1,~~PCSP_SYS,~~pcsa_lname,pcsa_fname,pcsa_minit,pcsa_degre,pcsa_addr1,pcsa_addr2,
pcsa_city,pcsa_state,PCSA_ZIP,County,val_desc, count(*) as 'COUNT'

from vw_Unique_DelCred_FINAL

GROUP BY pcsa_id1,PCS_TAX1,~~PCSP_SYS,~~pcsa_lname,pcsa_fname,pcsa_minit,pcsa_degre,pcsa_addr1,pcsa_addr2,
pcsa_city,pcsa_state,PCSA_ZIP,County,val_desc

HAVING COUNT(PCSP_SYS) <= '25'
ORDER BY count desc

if you want count of column why are you grouping by it. Try removing column from GROUP BY

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-09-25 : 11:35:26

I tried taking the pcsp_sys out, but it still is not working. Do you think it is because I have too much in the Select statement?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 11:39:25

quote:
Originally posted by werhardt

I tried taking the pcsp_sys out, but it still is not working. Do you think it is because I have too much in the Select statement?

why do you think it doesnt work? are you getting any error?

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-09-25 : 11:40:48

because the count colum is saying 1. There should be other number in there saying 5, 10, 13, etc.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 11:44:27

quote:
Originally posted by werhardt

because the count colum is saying 1. There should be other number in there saying 5, 10, 13, etc.

are you sure you've added only needed columns in GROUP BY. Also make sure that none of them have distinct values appearing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources