Question: I am able to count the occurrences of unique values per ID in the same record, but it seems there must be a more efficient way? Something like `COUNT([Value],'2')`?

Here's a simple example

```
ID | Value
 1      2
 1      3
 1      3
 1      2
 2      2
 2      3
 2      3
 3      3

```

And this is my current code:

```
SELECT ID, SUM(CASE WHEN Value = '2' THEN 1 ELSE 0 END) AS "COUNT2", 
SUM(CASE WHEN Value = '2' THEN 1 ELSE 0 END) AS "COUNT3"
FROM TABLE
GROUP BY ID

```

The results are:

```
ID | Count2 | Count3
 1      2        3
 2      1        2
 3      0        1

```

Is there a better way to get the count of unique values?

Answer: 
Try:

```
Select distinct Id, Count2, Count3
from Table 
Outer Apply (select count(id) as Count2 from table t 
             where t.id = Table.id and value = 2) c2
Outer Apply (select count(id) as Count3 from table t 
             where t.id = Table.id and value = 3) c3
Order by Id asc

```

Typed from my phone so may need to be tweaked a little but something like this should work
You could use `DECODE` if by "better" you mean more terse

```
WITH I (ID, V) AS (VALUES 
  (1,2)
, (1,3)
, (1,3)
, (1,2)
, (2,2)
, (2,3)
, (2,3)
, (3,3)
)
SELECT
    ID
,   COUNT(DECODE(V,2,1)) AS "Count2"
,   COUNT(DECODE(V,3,1)) AS "Count3"
FROM I
GROUP BY
    ID

```

returns

```
 ID Count2 Count3
 -- ------ ------
  1      2      2
  2      1      2
  3      0      1

```