SQL to count table column-CodePudding

I have a single table as shown below:

ID | Name | Category
--------------------
1  | Cat  | Mammal
2  | Dog  | Mammal
3  | Pea  | Vegetable
4  | Snake| Reptile

I would like an SQL query that will list each individual item with the count of the elements in its category. i.e.

Name  | Count of Category
-------------------------
Cat   | 2
Dog   | 2
Pea   | 1
Snake | 1

Edit1: I am using postgrsql

CodePudding user response：

If your DBMS support window function, we can try to use COUNT window function and add each individual item in PARTITION BY

SELECT Name,COUNT(*) OVER(PARTITION BY Category)
FROM T

sqlfiddle

CodePudding user response：

If possible, I would also prefer a window function like D-Shih showed. If your DB doesn't support this, you can use a subquery to count the category, something like this:

SELECT name, c "Count of Category" FROM
yourtable y JOIN
(SELECT category, COUNT(category) c FROM yourtable GROUP BY category) sub
ON y.category = sub.category;

This will produce the identic outcome.