I'm unsure exactly how to phrase the question... but essentially, I have a table that has data stored in it similar to the following:
TYPE # = 219 is Person
TYPE # = 224 is Action
Ideally, I would like to run a query that would return the following results from the table above | Essentially, the name of the person along with a count of however many types:
I'm not asking for the solution outright (I know this site isn't for that) - I just don't know where to go or look for information related to this type of problem. I'm new to writing SQL and, after spending quite a bit on this problem, I would greatly appreciate a nudge in the right direction.
One way is a conditional aggregation with a self-join. The other way would be pivot.
select a.Name ,sum(case when b.NAME = 'A' then 1 else 0 end) as A ,sum(case when b.NAME = 'B' then 1 else 0 end) as B ,sum(case when b.NAME = 'C' then 1 else 0 end) as C from SomeTable a inner join SomeTable b on a.[RECORD NUM] = b.[RECORD NUM] and a.NAME <> b.NAME where type_num = 219 group by a.NAME
You could also replace
and a.NAME <> b.NAME with
and b.[TYPE NUM] = 224 or even
and a.[TYPE NUM]<> b.[TYPE NUM] based on your post.