Using having to return distinct rows

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

Using having to return distinct rows

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2011-07-28 : 23:04:21

Hi,

Using the below query with distinct returns two rows. But using having with group by should also return 2 rows correct?

indicator HARP_ind
7 U
42 N

select count(*) as indicator,
case when (HARP_PRGM in ('Fannie Mae DU Refi Plus','Fannie Mae Refi Plus',
'Freddie LP Open Access','Freddie Mac Relief Refi') and LOAN_PURPOSE in ('Purchase','CASHOUT REFINANCE'))
then 'Y'
when HARP_PRGM in ('Fannie Mae DU Refi Plus','Fannie Mae Refi Plus',
'Freddie LP Open Access','Freddie Mac Relief Refi') then 'N'
else 'U' end as HARP_ind
from Fpdloanlist_may
where Category in ('Agency')
and Rollup_Channel in ('CORRESPONDENT','HLD','RETAIL','WHOLESALE')
group by HARP_ind
having count(*) > 1

indicator HARP_ind
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
42 N
7 U
7 U
7 U
7 U
7 U
7 U
7 U

Other than using DISTINCT, is there another way to get distinct groupings?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 01:12:14

I dont think posted query will work. You cant use aliases directly in your group by.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources