SQL to count table column

Question

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

What's your dbms?

D-Shih
– D-Shih

2022-05-28 00:47:36 +00:00
Commented May 28, 2022 at 0:47 — D-Shih
– D-Shih, Commented May 28, 2022 at 0:47

D-Shih · Accepted Answer · 2022-05-28 02:10:58Z

1

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

edited May 28, 2022 at 2:10

answered May 28, 2022 at 0:49

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jonas Metzler · Accepted Answer · 2022-05-28 06:02:43Z

0

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.

answered May 28, 2022 at 6:02

Jonas Metzler

6,8363 gold badges9 silver badges24 bronze badges

2 Comments

jlandercy Over a year ago

There is a single table with a single aggregate to compute, there is no need for JOIN and subquery.

Jonas Metzler Over a year ago

Feel free to show a better option

Collectives™ on Stack Overflow

SQL to count table column

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related