mysql count rows with a specific column

开发者_Go百科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

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

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)

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

I'll offer an alternative syntax from the others that will work just the same.

SQL: (Demo)

SELECT Name, COUNT(1)
FROM Names
GROUP BY 1 ASC

Result set:

| Name | COUNT(1) |
| ---- | -------- |
| A    | 4        |
| B    | 3        |
| C    | 3        |
| E    | 2        |

1. Using group by will effectively return only rows with unique Name values.
2. The COUNT(1) will count the number of rows in each aggregate generated by using GROUP BY on the first column in the SELECT -- think of the aggregate data as a cluster of rows which must be "flattened somehow" by a MySQL function before it can be introduced to the result set.
3. GROUP BY will happily receive the sorting order declaration after the targeted column, so the ORDER BY clause can be omitted.

p.s. I am not sure if you actually need a single column of data which is pre-formatted as written in your question OR if this was just your way of expressing your desired result set and you actually want two columns of data without the bloat of plain text.

If you need the pre-formatted text version, then the magic 1 will not be suitable -- the column will need to be referenced explicitly.

SELECT CONCAT(Name, ' = ', COUNT(Name), ' Times')
FROM Names
GROUP BY Name DESC

Personally, I would not use CONCAT() for this task unless, say, you are generating a json reponse for an ajax call (passing the result set to a different layer). In other words, you are instantly wrapping the full result set in json_encode() then back to javascript where you are passing it directly to a Google api for graphic representation.

Excerpt from the PHP documentation -- specifically mysqli's fetch_all() :

[...] should only be used in those situations where the fetched result set will be sent to another layer for processing.

Otherwise, if you are going to manually iterate over the rows at any point in the flow, the inclusion of static text should be done at that point in your application. This will keep your result set lean & clean and make it is easier for you to maintain your application.