mysql count rows with a specific column

Question

I have a table like this

Sr Name
1       A
2       B
3       C
4       C
5       C
6       E
7       A
8       A
9       A
10       E
11       B
12       B

I need output like this
A = 4 Times
B = 3 Times
C = 3 Times
E = 2 Times

How can I achieve this?

Thanks in advance

Solution 1

SELECT Name, COUNT(Sr) FROM myTable GROUP BY Name ORDER BY Name ASC;

Solution 2

You may want to use:

SELECT   name, CONCAT(COUNT(*), ' Times') number
FROM     your_table
GROUP BY name
ORDER BY name;

Test case:

CREATE TABLE your_table (sr int, name varchar(50));
INSERT INTO your_table VALUES(1, 'A');
INSERT INTO your_table VALUES(2, 'B');
INSERT INTO your_table VALUES(3, 'C');
INSERT INTO your_table VALUES(4, 'C');
INSERT INTO your_table VALUES(5, 'C');
INSERT INTO your_table VALUES(6, 'E');
INSERT INTO your_table VALUES(7, 'A');
INSERT INTO your_table VALUES(8, 'A');
INSERT INTO your_table VALUES(9, 'A');
INSERT INTO your_table VALUES(10, 'E');
INSERT INTO your_table VALUES(11, 'B');
INSERT INTO your_table VALUES(12, 'B');

Result:

+------+---------+
| name | number  |
+------+---------+
| A    | 4 Times |
| B    | 3 Times |
| C    | 3 Times |
| E    | 2 Times |
+------+---------+
4 rows in set (0.00 sec)

Or if you prefer a one column result set, you may want to do:

SELECT   CONCAT(name, ' = ', COUNT(*), ' Times') result
FROM     your_table
GROUP BY name
ORDER BY name;

Result:

+-------------+
| result      |
+-------------+
| A = 4 Times |
| B = 3 Times |
| C = 3 Times |
| E = 2 Times |
+-------------+
4 rows in set (0.00 sec)

Solution 3

select count(Name) , Name
from yourtable
group by Name
order by name