Clever Geek Handbook

The total number of columns (only once for every repeated row data)

Suppose I have the following table orders

and I receive all failed

orders.

+----+------------------+-------+--------+
| 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 total number, but only once per repeated line (the line is considered a duplicate when the same email

and total

match)

I currently have a very simple query that gets the total of all orders failed

. I'm not sure how to go about this to get the data back I want (I've tried communicating with DISTINCT

and to GROUP BY

no avail.)

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

Which returns:

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

As a result, I would like to return:

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

(second line will be excluded from the result, as email

and total

will have the same values as in the first line)

Is it possible to get this data in one request?

+3


source to share


2 answers


I think you need to do distinct

or group by

in a subquery:



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


source


Maybe this:

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


In this case, we generate a pseudo unique key from the email and total and only consider unique occurrences ... It should work ...

0


source






More articles:


All Articles