Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sum column total (only once per duplicate row data)

Tags:

sql

mysql

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?

like image 517
billyonecan Avatar asked Sep 02 '25 17:09

billyonecan


2 Answers

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;
like image 142
Gordon Linoff Avatar answered Sep 04 '25 12:09

Gordon Linoff


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

like image 39
cn007b Avatar answered Sep 04 '25 11:09

cn007b