MySQL query or CF code to “Flag” a group of query results based on a common value?

I have the following query that looks up customer accounts:

SELECT    ACCOUNT_ID, SUBSCRIBER_ID, PKG_CODE    
 FROM   Accounts    
 WHERE  ACCOUNT_DESC LIKE '%Eye%'    
 GROUP BY  ACCOUNT_ID

Which gives me the following result..

 1. ACCOUNT_ID,  SUBSCRIBER_ID, PKG_CODE  
 2. ---------- -------------- -------    
 3. 11016869,    10016598,      N  
 4. 11015922,    10015713,      N  
 5. 11015062,    10014878,      N  
 6. 11018312,    10017973,      Y  
 7. 11018310,    10017973,      N  
 8. 11018309,    10017973,      N  
 9. 11018308,    10017973,      N

Question: How do I display a "1" in a Flag column if "any one" of the "SUBSCRIBER_ID's" have a value of "Y" in the PKG_CODE column?

Here are the query results that I'm trying to get to for this example (notice that the last 4 records need to be flagged a "1" because one of them had a "Y" for PKG_CODE and matching SUBSCRIBER_ID's..

1. ACCOUNT_ID, SUBSCRIBER_ID, PKG_CODE, FLAG
2. ---------- -------------- ------- -----
3. 11016869,10016598,N
4. 11015922,10015713,N
5. 11015062,10014878,N
6. 11018312,10017973,Y,1
7. 11018310,10017973,N,1
8. 11018309,10017973,N,1
9. 11018308,10017973,N,1

I tried the following, but it only flags the "Y" records? I need it to also check the SUBSCRIBER_ID" column value..

SELECT    ACCOUNT_ID, SUBSCRIBER_ID, PKG_CODE  
 , IF(PKG_CODE = 'Y', "Y", "N") AS FLAG    
 FROM   Accounts    
 WHERE  ACCOUNT_DESC LIKE '%Eye%'    
 GROUP BY  ACCOUNT_ID

If this can be done on the CF page, that would work as well instead of in the query.. i just need the Flagged values to display alongside each record.