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

Leave a Reply