CODEWITHSUNDEEP
mysqlsql
WebQube
WebQube

Reputation: 8991

TIMESTAMPDIFF for more then one type

I have this query to present me the average time diff between payments 

SELECT TIMESTAMPDIFF(SECOND, MIN(x.ts), MAX(x.ts) )/(COUNT(DISTINCT(x.ts)) -1) 
as average_minutes_between_paypal_orders 
from (
select p.created_at as ts 
from payment_tokens p 
where status='processed' and payment_method = 'paypal' 
order by created_at desc limit 100
) x

The thing is, I want it to present, SEPARATELY, the average time diff between other methods like 'braintree', like 'other' etc. and I don't want to write several queries.

Is there a way to present for all the payment_methods seperatly in 1 query?

Upvotes: 0

Views: 60

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

One way to do desired calculation use CASE

SELECT 
TIMESTAMPDIFF(SECOND
              ,MIN(CASE WHEN x.payment_method = 'paypal' THEN x.ts END)
              ,MAX(CASE WHEN x.payment_method = 'paypal' THEN x.ts END) )
/(COUNT(DISTINCT(CASE WHEN x.payment_method = 'paypal' THEN x.ts END)) -1)  AS average_minutes_between_paypal_orders ,
TIMESTAMPDIFF(SECOND
              ,MIN(CASE WHEN x.payment_method = 'braintree' THEN x.ts END)
              ,MAX(CASE WHEN x.payment_method = 'braintree' THEN x.ts END) )
/(COUNT(DISTINCT(CASE WHEN x.payment_method = 'braintree' THEN x.ts END)) -1)  AS average_minutes_between_braintree_orders 
FROM (
SELECT p.created_at AS ts 
FROM payment_tokens p 
WHERE `status`='processed'
ORDER BY created_at DESC LIMIT 100
) `x`

Upvotes: 1

Related Questions