# How can i sum amount group by book_id and order_id

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:

Working Test Case – Updated

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