Display only one row for values that appear multiple times

Question

I have multiple rows with the same name in this table, and I want to show only one of row of each. For example, with the following data:

| name | number |
+------+--------+
| exe  |   1    |
| exe  |   10   |
| exe  |   2    |
| bat  |   1    |
| exe  |   3    |
| bat  |   4    |

I would like to see the following results:

| name | number | 
+------+--------+
| exe  |  16    |
| bat  |  5     |

How can I achieve this result?

Duplicate response: My question only have 1 table, the JOIN ..ON command creates confusion in understanding, i think this simple question can help many guys!

possible duplicate of [Sum duplicate row values](http://stackoverflow.com/questions/20369868/sum-duplicate-row-values) — AdamMc331, Jul 07 '15 at 21:21

score 2 · Answer 1 · answered Jul 07 '15 at 21:16

2

You can use an aggregation function for this:

SELECT name, SUM(number) AS total
FROM myTable
GROUP BY name;

Here is a reference on aggregate functions, and here is an SQL Fiddle example using your sample data.

answered Jul 07 '15 at 21:16

AdamMc331

16,492
10
71
133

score 2 · Accepted Answer · answered Jul 07 '15 at 21:17

Try something like this:

  SELECT t.`name`, SUM(t.`number`) AS `number`
    FROM mytable t
   GROUP BY t.`name`
   ORDER BY `number` DESC

let the database return the result you want, rather than mucking with returning a bloatload of rows, and collapsing them on the client side. There's plenty of work for the client to do without doing what the database can do way more efficiently.

Display only one row for values that appear multiple times

2 Answers2