how to count properly mysql version 5.8

62 Views Asked by At

so i have a query like this :

SELECT * FROM my_db.my_order where product_id = 395 order by id desc;

and the output of this table is like this

+----------+----------+----------+---------------------+------------+--------+
| order_id | buyer_Id | quantity |      createdAt      | product_id | status |
+----------+----------+----------+---------------------+------------+--------+
|     6232 |    89450 |        1 | 2020-05-06 17:44:41 |        395 |      1 |
|     6232 |    89450 |        1 | 2020-05-06 17:44:41 |        395 |      1 |
|     6232 |    23048 |        2 | 2020-05-06 17:44:41 |        395 |      1 |
|     6232 |    89464 |        1 | 2020-05-06 17:44:40 |        395 |      1 |
|     6232 |    89463 |        1 | 2020-05-06 17:44:40 |        395 |      1 |
|     6232 |    89463 |        2 | 2020-05-06 17:43:25 |        395 |      0 |
|     6232 |    89464 |        2 | 2020-05-06 17:43:19 |        395 |      0 |
+----------+----------+----------+---------------------+------------+--------+

so i want to count total of quantity for this product_id where the status are = 1, so i made this query

SELECT 
    SUM(grouped_my_order_tbl.quantity)
FROM
    (
        SELECT 
            mo.order_seller_id AS order_seller_id,
            mo.quantity AS quantity,
            mo.status AS status,
            mo.product_id AS product_id
        FROM my_order mo
    ) grouped_my_order_tbl
WHERE
    grouped_my_order_tbl.product_id = 395
    AND grouped_my_order_tbl.order_seller_id = 6232
    AND grouped_my_order_tbl.status = 1;

and the output for the count is 6 instead of 5

where's my wrong at?

expected result : sum = 5

update : the exact sum should be 5 because there's duplicate buyer_id and createdAt so the system read the data twice, instead it's just only 1 record not 2 record

0

There are 0 best solutions below