I have a table:

```
book_id order_id amount
-------- ------- -------
a d1 10
a d1 10
a d1 10
a d2 20
a d2 20
a d3 30
```

i try this

```
SELECT book_id,sum(amount) as sum FROM table WHERE book_id= 'a' GROUP BY book_id;
```

Result

```
book_id sum
-------- -------
a 100
```

but i want to group them by book_id and sum amount like this

```
book_id sum
-------- -------
a 60 ==> d1+d2+d3
```

How would I write a mySQL to SUM row like d1+d2+d3?

## Here is Solutions:

We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.

### Solution 1

Given the current data, you would `SUM`

based on `DISTINCT`

rows:

The result of all:

```
book_id SUM(amount)
-------------------
a 60
-------------------
```

```
WITH cte AS (
SELECT DISTINCT book_id, order_id, amount FROM tbl
)
SELECT book_id
, SUM(amount)
FROM cte
GROUP BY book_id
;
```

Add filtering as you wish to either the `cte`

term or the final query expression.

```
WITH cte AS (
SELECT DISTINCT book_id, order_id, amount
FROM tbl
WHERE book_id = 'a'
)
SELECT book_id
, SUM(amount)
FROM cte
GROUP BY book_id
;
```

With older versions of MySQL, you can use a `derived table`

, instead of a `CTE`

term:

```
SELECT book_id
, SUM(amount)
FROM (
SELECT DISTINCT book_id, order_id, amount
FROM tbl
WHERE book_id = 'a'
) AS cte
GROUP BY book_id
;
```

Trying to `SUM(DISTINCT amount)`

is incorrect, since some different orders could have the same `amount`

and those would be compressed into one value.

### Solution 2

There are two ways. The first one is with `Distinct`

:

```
Select t.book_id, sum(t.amount)
from
(
SELECT DISTINCT book_id, order_id, amount
FROM book
) t
group by t.book_id
```

The second one is with `With`

:

```
WITH w AS (
SELECT DISTINCT book_id, order_id, amount FROM book
)
SELECT book_id, SUM(amount)
FROM w
GROUP BY book_id;
```

### Solution 3

Try this nested `SELECT`

.

```
SELECT book_id, SUM(amount) AS `sum` FROM (SELECT DISTINCT book_id, order_id, amount FROM book GROUP BY book_id, order_id) AS a;
```

Result

```
book_id sum
------- --------
a 60
```

### Solution 4

```
SELECT DISTINCT(book_id), SUM(amount) FROM table GROUP BY book_id WHERE book_id ='a';
```

**Note: Use and implement solution 1 because this method fully tested our system.Thank you 🙂**

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0