Number of string occurrences

Question

I have a list of cars

id | brand | model
1  | bmw   | 525
1  | bmw   | 533
2  | audi  | a8
...

I want to take brand occurrences specific to id, for this example:

id | brand | model | n
1  | bmw   | 525   | 2
1  | bmw   | 533   | 2
2  | audi  | a8    | 1
...

Please, i need help with this.

Answer 1

Use count() as a window function:

select c.*, count(*) over (partition by brand) as n
from cars c;

Answer 2

Use a correlated sub-query to count:

select c1.id, c1.brand, c1.model, (select count(*) from cars c2
                                   where c2.id = c1.id)
from cars c1

Answer 3

You can do this:

SELECT T1.id,T1.brand,T1.model,T2.n
FROM TableName T1 JOIN
(SELECT id,brand,COUNT(1) as n
 FROM TableName
 GROUP BY id,brand) as T2 ON T1.id=T2.id AND T1.brand=T2.brand

Result:

id  brand   model   n
1   bmw     525     2
1   bmw     533     2
2   audi    a8      1

Sample result in SQL Fiddle.