'Take count of column after merging

I have merged two dataframes and selected for only those rows when a column contains a specific value. Now, I want to count the number of occurrences the value in the column has after filtering for beak.

Here's what I have tried:

SELECT 
    landbirds.size, landbirds.weight,landbirds.feather, sea_birds.beak
FROM 
    land_birds
INNER JOIN
    sea_birds
ON
    land_birds.colour = sea_birds.colour
WHERE beak LIKE '%long%'
GROUP BY feather
COUNT(feather) ASC;

However, this does not produce an additional column counting the different feather types i.e. long, medium, short.

How can I effectively count the feather types after merging?

Example data:

#landbirds
... colour   size   weight   feather
      W       M      12.2     Long
      Br      M      12.7     Long
      Br      S      15.4     Short
..

second table:

#seabirds
...  colour   beak     
      W       Long  
      Br      Medium 
      Br      Long    
..

Expected output:

#results based on data above
feather   featherCount
Long          1
Short         1
sql


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source