SQL add count column based on same table and grouped by another column

Question

I appreciate this is a bit weird but I need to do this this way because the next data reporting steps relay on data in this specific format. Let say I have a table like:

ID | Type   | Item  
----------------------
1  | red    | apple  
2  | red    | apple  
3  | green  | apple  
4  | red    | berry  
5  | red    | berry  
6  | green  | banana  
7  | yellow | lemon   

what I need now is to add a column to the SELECT * query that will in each record tell me how many records there are in the WHOLE query that have the same type. So the output that I am looking for would be:

ID | Type   | Item   | count of type  
-------------------------------------
1  | red    | apple  |  4  
2  | red    | apple  |  4    
3  | green  | apple  |  2  
4  | red    | berry  |  4  
5  | red    | berry  |  4  
6  | green  | banana |  2  
7  | yellow | lemon  |  1  

I know I can do a subquery but the actual data is much more complicated (formulas and joins all over the place) and every time I add a sub query it makes the whole query a bit messy. So just wondering if I could do this count somehow "directly" in the main query? or maybe you guys have yet another idea. what do you think. I am working on dashDB

Answer 1

You can use partition by:

select ID, Type, Item, count(ID) over(partition by Type) as CountOfType
From table

Answer 2

You can GROUP By Type and Item to get count of each Item and Type

SELECT t1.ID, t1.Type, t1.Item, t2.CountType
FROM tableName t1
JOIN ( SELECT Type, Item,COUNT(*) As CountType 
       FROM tableName
       GROUP BY Type, Item
     ) As t2

ON t1.Type = t2.Type AND t1.Item = t2.Item

Answer 3

you can use analytical function, add the following to your SELECT query Count(*) over (partition by Type) as countOfType