SQL Count() column values

Question

I have the following source table:

pid | code
----------
1      P2
2      P2
3      P3
4      P1
5      P2
6      P1

Now, I want to get the information how many times each code exists:

code | count
------------
P2      3
P1      2
P3      1

So I want to count the values from code column and assign it to the distinct set of available code values. Finally I want to order by count number.

Why do you all have brackets on Count?first of all its mysql and second its not a reserved word. — Mihai, Aug 29 '14 at 14:18
Does this answer your question? [Count the occurrences of DISTINCT values](https://stackoverflow.com/questions/1346345/count-the-occurrences-of-distinct-values) — mickmackusa, Apr 20 '21 at 09:47

Linger · Accepted Answer · 2014-08-29T14:21:33.663

3

SQL Fiddle:

SELECT t.code, COUNT(*) AS `count`
FROM MyTable t
GROUP BY t.code
ORDER BY COUNT(*) DESC

edited Aug 29 '14 at 14:21

answered Aug 29 '14 at 14:15

Linger

14,942
23
52
79

score 1 · Answer 2 · answered Aug 29 '14 at 14:23

1

DECLARE @testTable table (pid  int
                     ,code varchar(2)
)

insert into @testTable values (1, 'P2')
insert into @testTable values (2, 'P2')
insert into @testTable values (3, 'P3')
insert into @testTable values (4, 'P1')
insert into @testTable values (5, 'P2')
insert into @testTable values (6, 'P1')


SELECT CODE, COUNT(1) AS [COUNT]
FROM @testTable 
GROUP BY CODE
ORDER BY [COUNT] DESC

answered Aug 29 '14 at 14:23

Deiwys

243
1
5
15

`[COUNT]` won't work. You need to use the back tick instead. Also `ORDER BY [COUNT] DESC` will not work, you need to use `ORDER BY COUNT(1) DESC`. – Linger Aug 29 '14 at 14:27

SQL Count() column values

2 Answers2