Using COUNT() in WHERE Clause

Question

I have the following SQL statement:

SELECT     C.LINKED_TABLE_ID AS CLIENT_DIWOR, I.STATUS AS AML_STATUS, dbo.CLIENT_MASTER.CLIENTCODE
FROM         AML_INFORMATION AS I INNER JOIN
                dbo.COMM_ENTRY AS C ON C.NAME_ID = I.CONTACT_ID AND C.TABLE_ID = 'C' AND C.PRIMARY_FLAG = 'Y' INNER JOIN
                dbo.CLIENT_MASTER ON C.LINKED_TABLE_ID = dbo.CLIENT_MASTER.DIWOR
WHERE I.CONTACT_ID = 234
    AND I.[STATUS] = 'CC'
    AND (CLIENT_MASTER.DIWOR = I.CONTACT_ID)
    AND (CLIENT_MASTER.POSTING_STATUS <> '')
    AND ((SELECT COUNT(CONTACT_ID) FROM AML_ID_DOCUMENT GROUP BY CONTACT_ID HAVING CONTACT_ID = 234) >1)

If I run this it returns 0 records, however if I remove the last AND statement AND ((SELECT COUNT(CONTACT_ID) FROM AML_ID_DOCUMENT GROUP BY CONTACT_ID HAVING CONTACT_ID = 234) >1) it returns the records I would expect.

Is it possible to use a COUNT() in this way? Incidentally, the COUNT() in this example returns 2 records and if I include it in the SELECT statement I also get 0 records returned.

Can anyone point me in the right direction?

Thanks in advance.

Are you looking for the existence of the contact or are you looking for the existence of the contact where it's used more than once. — TEEKAY
– TEEKAY, Commented Nov 9, 2012 at 16:47
Re-added the FROM clause, missed it somehow during copy & paste — Danny
– Danny, Commented Nov 9, 2012 at 16:50
@TEEKAY I am looking for the existence of the CONTACT_ID where it exists more than once. — Danny
– Danny, Commented Nov 9, 2012 at 16:51
@vasja/bummi Running SELECT COUNT(CONTACT_ID) FROM AML_ID_DOCUMENT GROUP BY CONTACT_ID HAVING CONTACT_ID = 234 in isolation returns "2" which is correct. So should having this as part of the where clause of the "main" query not evaluate correctly? — Danny
– Danny, Commented Nov 9, 2012 at 16:53

bummi · Accepted Answer · 2012-11-09 17:11:58Z

11

Your showed Script should never return anything ... your having part could look this way

   and (Select Count(*) from AML_ID_DOCUMENT  where CONTACT_ID =I.CONTACT_ID)>1

though ist should work as it is...

edited Nov 9, 2012 at 17:11

answered Nov 9, 2012 at 16:40

bummi

27.4k14 gold badges67 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Danny Over a year ago

I want to return records from AML_INFORMATION where there are a minimum of 2 records (for a given contact) in AML_ID_DOCUMENT.

Danny Over a year ago

I'm not sure what you amended in this script (can't recall the original) but it appears to have done the trick. Thank you very much.

vasja · Accepted Answer · 2012-11-09 16:38:14Z

9

Shouldn't the last part be like this:

AND EXISTS (SELECT CONTACT_ID FROM AML_ID_DOCUMENT
      WHERE CONTACT_ID = 234
      GROUP BY CONTACT_ID
      HAVING COUNT(CONTACT_ID)>1)

answered Nov 9, 2012 at 16:38

vasja

4,79217 silver badges16 bronze badges

1 Comment

Danny Over a year ago

This evaluates to true and returns the CONTACT_ID when run in isolation but does not work when it forms part of the where clause.

Saransh Kejriwal · Accepted Answer · 2017-09-18 09:20:02Z

0

Try this query:

select count(portfolio_id) as porfolioCount 
from engineering_module_tracker_v2 
where r0_approval__category='Cat I' & 'Cat II';

Cat means category-wise.

edited Sep 18, 2017 at 9:20

Saransh Kejriwal

2,5315 gold badges19 silver badges40 bronze badges

answered Sep 5, 2017 at 12:45

punit singh

143 bronze badges

Collectives™ on Stack Overflow

Using COUNT() in WHERE Clause

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related