Count rows returned by subquery

Question

Let's say I have this table named tblsample

I'm trying to get the number of codes unique to each title. So the result should be abc = 3 and def = 3

My query right now is

Select T1.title, COUNT(tbl2.code) as [No. of Codes]
from (Select t2.title, T2.CODE from tblsample as T2 where T2.category = 'com' group by t2.title, t2.CODE) AS tbl2
inner join tblsample as T1 on (T1.title = tbl2.title)
where T1.category = 'COM'
group by T1.title
order by T1.title

But it's returning abc = 15 and def = 9 so I'm quite confused. Where am I wrong here?

Answer 1

I think the query as simple as this will do:

Select title, COUNT(DISTINCT code) as [No. of Codes]
from tblsample 
where category = 'COM'
group by title
order by title