合计列总数(每重复行数据仅一次)

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

假设我有下面的order表，我正在检索所有失败的订单。

+----+------------------+-------+--------+
| id | email            | total | status |
+----+------------------+-------+--------+
| 1  | john@example.com | 39.99 | failed |
|----|------------------|-------|--------+
| 2  | john@example.com | 39.99 | failed |
|----|------------------|-------|--------+
| 3  | pete@example.com | 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)

(由于电子邮件和total的值与第一行的值相同，所以将会省略第二行)

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

是否可以在单个查询中检索此数据?

2 个解决方案

#1

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;

#2

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...

在这种情况下，我们从电子邮件和total中构建伪惟一键，并只计算惟一出现的次数……它应该工作……

#1