CODEWITHSUNDEEP
mysqlsql
billyonecan
billyonecan

Reputation: 20250

Sum column total (only once per duplicate row data)

Suppose I have the following orders table, and I'm retrieving all failed orders.

+----+------------------+-------+--------+
| id | email            | total | status |
+----+------------------+-------+--------+
| 1  | [email protected] | 39.99 | failed |
|----|------------------|-------|--------+
| 2  | [email protected] | 39.99 | failed |
|----|------------------|-------|--------+
| 3  | [email protected] | 19.99 | failed |
+----+------------------+-------+--------+

I need to get the total number of failed orders, as well as their summed total, but only once per duplicate row (row is considered a duplicate when same email and total are the same)

Currently I have a very simple query which gets the total of all failed orders. I'm not sure how to go about modifying it to return the desired data (I've tried messing around with DISTINCT and GROUP BY to no avail.)

SELECT COUNT(*) AS `count`, SUM(`total`) AS `grand_total` FROM `orders` WHERE `status` = 'failed';

Which returns:

+-------+-------------+
| count | grand_total |
+-------+-------------+
|   3   |   99.97     |
+-------+-------------+

The result I'd like to return is:

+-------+-------------+
| count | grand_total |
+-------+-------------+
|   2   |    59.98    |
+-------+-------------+

(the second row would be omitted from the result as the email and total have the same values as those in the first row)

Is it possible to retrieve this data in a single query?

Upvotes: 3

Views: 4327

Answers (2)

cn0047
cn0047

Reputation: 17061

Maybe it:

SELECT COUNT(DISTINCT email, total)
FROM orders WHERE status = 'failed'
;

In this case we build pseudo-unique key from email and total, and count only unique occurrences... It should work...

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I think you need to do the distinct or group by in a subquery:

select count(*), sum(total)
from (select distinct email, total
      from orders
      where status = 'failed'
     ) et;

Upvotes: 5

Related Questions