CODEWITHSUNDEEP
mysqlsql
fareed
fareed

Reputation: 3072

Perform query calculations on two tables

I have the following table structures

table sales:

ID, SALES_DATE

table sales_payment:

ID, SALES_ID, PAYMENT_DATE

Where SALES_ID is a foreign key to sales table ID. table sales_payment has a many to one relationship to table sales where one sales can have multiple payments (e.g. a payment today and another payment after five days)

I want a query that can calculate the average overall payment days for each sales taking the maximum number of days to fully pay the sales.

Considering this scenario:

the first sales is made in 2014-01-01, and its payments are in

(days to pay for the sales are 4 days)

the second sales is made in 2014-01-03, and its payments are in :

(days to pay for the sales are 8 days)

so the return average is the average of the days of each sales to be fully paid :

(4+8)/2 = (6 days)

I have made this on programming level but it is taking too much time to retrieve data and perform loops for thousands of raws. How this can be achieved on sql level?

Upvotes: 0

Views: 65

Answers (2)

carexcer
carexcer

Reputation: 1427

I think this will solve your problem (If I have understand it, you want to start counting days since sales.sales_date ):

select sp.sales_id, avg(paymentdays)
from (select sp.sales_id, ( max(sp.payment_date) - min(s.sales_date) ) as paymentdays
      from sales_payment sp, sales s
    where sp.sales_id = s.id
      group by sp.sales_id
     ) sp
     group by sp.sales_id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If I understand correctly, you first want to calculate the payment days for each sale. Then take the average of this value:

select avg(paymentdays)
from (select sp.sales_id, (datediff(max(sp.payment_date), min(sp.payment_date)) + 1) as paymentdays
      from sales_payment sp
      group by sp.sales_id
     ) sp;

Upvotes: 1

Related Questions