question

venkatnaveen avatar image
venkatnaveen asked

When taking count on group function that is if i write count(column)=1 i not getting the output

select NMAP+' '+NLOT ,NREF,IIREF,NNAM,NAD1,NAD2,IISQFT,IIYRBL,NRBED,NRBATH from OR_Baker_09_Main_02 nolock inner join OR_Baker_09_Main_03 on NREF=IIREF where NMAP+' '+NLOT in (select NMAP+' '+NLOT from OR_Baker_09_Main_02 group by NMAP+' '+NLOT having COUNT(NMAP+' '+NLOT)=1)order by (NMAP+' '+NLOT)
sql-server-2008c
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The code you've pasted in looks broken, I think. The first query has two GROUP BYs in a row. If I remove the duplicated bit, you seem to have two identical queries. Please use the tools to format your code so we can read it and see two different queries. (That might help you see what's wrong anyway.) And then please explain the difference between the behaviour of the two queries.
0 Likes 0 ·
I have edited David just check
0 Likes 0 ·

1 Answer

· Write an Answer
David Wimbush avatar image
David Wimbush answered
It looks like you only want to select records that are also in OR_Baker_09_Main_02. In which case try replacing the IN subquery with this: select distinct NMAP + ' ' + NLOT from OR_Baker_09_Main_02 PS: Check that your subquery actually works. I seem to remember that, if there's an error in an IN subquery, the error is not raised but instead the IN clause just doesn't cause anything to qualify in the outer query.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.