CODEWITHSUNDEEP
mysqlsqlselect
seth
seth

Reputation: 1718

MySQL -- Finding % of orders with a transaction failure

I have an order_transactions table with 3 relevant columns. id (unique id for the transaction attempt), order_id (the id of the order for which the attempt is being made), and success an int which is 0 if failed, and 1 if successful.

There can be 0 or more failed transactions before a successful transaction, for each order_id.

The question is, how do I find:

I realize this is some combination of distinct, group by, maybe a subselect, etc, I'm just not well versed in this enough. Thanks.

Upvotes: 0

Views: 434

Answers (3)

spencer7593
spencer7593

Reputation: 108480

You want "counts" of orders that meet specific conditions over multiple rows, so I'd start with a GROUP BY order_id

SELECT ...
  FROM mytable t 
 GROUP BY t.order_id

To find out if a particular order ever had a failed transaction, etc. we can use aggregates on expressions that "test" for conditions.

For example:

SELECT MAX(t.success=1)         AS succeeded
     , MAX(t.success=0)         AS failed
     , IF(MAX(t.success=1),0,1) AS never_succeeded
  FROM mytable t 
 GROUP BY t.order_id

The expressions in the SELECT list of that query are MySQL shorthand. We could use longer expressions (MySQL IF() function or ANSI CASE expressions) to achieve an equivalent result, e.g.

  CASE WHEN t.success = 1 THEN 1 ELSE 0 END

We could include the `order_id` column in the SELECT list for testing. We can compare the results for each order_id to the rows in the original table, to verify that the results returned meet the specification.

To get "counts" of orders, we can reference the query as an inline view, and use aggregate expressions in the SELECT list.

For example:

SELECT SUM(r.succeeded)              AS cnt_succeeded
     , SUM(r.failed)                 AS cnt_failed
     , SUM(r.never_succeeded)        AS cnt_never_succeeded 
  FROM ( 
         SELECT MAX(t.success=1)         AS succeeded
              , MAX(t.success=0)         AS failed
              , IF(MAX(t.success=1),0,1) AS never_succeeded
           FROM mytable t 
          GROUP BY t.order_id
       ) r

Since the expressions in the SELECT list return either 0, 1 or NULL, we can use the SUM() aggregate to get a count. To make use of a COUNT() aggregate, we would need to return NULL in place of a 0 (FALSE) value.

SELECT COUNT(IF(r.succeeded,1,NULL))       AS cnt_succeeded
     , COUNT(IF(r.failed,1,NULL))          AS cnt_failed
     , COUNT(IF(r.never_succeeded,1,NULL)) AS cnt_never_succeeded 
  FROM ( 
         SELECT MAX(t.success=1)         AS succeeded
              , MAX(t.success=0)         AS failed
              , IF(MAX(t.success=1),0,1) AS never_succeeded
           FROM mytable t 
          GROUP BY t.order_id
       ) r

If you want a count of all order_id, add a COUNT(1) expression in the outer query. If you need percentages, do the division and multiply by 100,

For example 

SELECT SUM(r.succeeded)                    AS cnt_succeeded
     , SUM(r.failed)                       AS cnt_failed
     , SUM(r.never_succeeded)              AS cnt_never_succeeded 
     , SUM(1)                              AS cnt_all_orders
     , SUM(r.failed)/SUM(1)*100.0          AS pct_with_a_failure
     , SUM(r.succeeded)/SUM(1)*100.0       AS pct_succeeded
     , SUM(r.never_succeeded)/SUM(1)*100.0 AS pct_never_succeeded
  FROM ( 
         SELECT MAX(t.success=1)         AS succeeded
              , MAX(t.success=0)         AS failed
              , IF(MAX(t.success=1),0,1) AS never_succeeded
           FROM mytable t 
          GROUP BY t.order_id
       ) r

(The percentages here are a comparison to the count of distinct order_id values, not as the total number of rows in the table).

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

To get the number of orders which never had a successful transaction you can use:

SELECT COUNT(*)
FROM (
  SELECT order_id
  FROM transactions 
  GROUP BY order_id
  HAVING COUNT(CASE WHEN success = 1 THEN 1 END) = 0) AS t

Demo here

The number of orders which had a transaction with a failure (eventually successful or not) can be obtained using the query:

SELECT COUNT(*)
FROM (
  SELECT order_id
  FROM transactions
  GROUP BY order_id
  HAVING COUNT(CASE WHEN success = 0 THEN 1 END) > 0) AS t

Demo here

Finally, to get the number of orders which never had a failed transaction (success only):

SELECT COUNT(*)
FROM (
   SELECT order_id
   FROM transactions
   GROUP BY order_id
   HAVING COUNT(CASE WHEN success = 0 THEN 1 END) = 0) AS t

Demo here

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

successful order 

select count(*) from 
( select distinct order_id  from my_table where success = 1 ) as t;

unsuccessful order 

select count(*) from 
( select distinct order_id  from my_table where success = 0 ) as t;

never filed transaction 

select count(*) from 
( select distintc order_id  from my_table where id not in 
       (select distinct order_id  from my_table where success = 0) ) as t;

Upvotes: 0

Related Questions