Query to exclude row based on another row's filter

I'm using Oracle 10g.

Question: How can I write query to return just ID only if ALL the codes for that ID end in 6?

I don't want ID=1 beca开发者_Go百科use not all its codes end in 6.

TABLE_A

ID  Code
===============
1   100  
1   106
2   206
3   316
3   326
4   444

Desired Result:
ID
==
2
3

---

You simply want each ID where the count of rows for that id is the same as the count of rows where the third digit is six.

SELECT ID
FROM TABLE_A
GROUP BY ID
HAVING COUNT(*) = COUNT(CASE WHEN SUBSTR(code,3,1) = '6' THEN 1 END)

---

Try this:

SELECT DISTINCT b.id
  FROM (
        SELECT id, 
                COUNT(1) cnt
          FROM table_a  
         GROUP BY id 
        ) a,
        (
        SELECT id, 
                COUNT(1) cnt
          FROM table_a  
         WHERE CODE LIKE '%6'
         GROUP BY id
        )b
WHERE a.id = b.id
  AND a.cnt = b.cnt

Alternative using ANALYTIC functions:

SELECT DISTINCT id   
  FROM 
    (
    SELECT  id, 
            COUNT(1)  OVER(PARTITION BY id) cnt,
            SUM(CASE WHEN code LIKE '%6' THEN 1 ELSE 0 END) OVER(PARTITION BY id) sm
        FROM  table_a 
  )
WHERE cnt = sm