CODEWITHSUNDEEP
sql
user745633
user745633

Reputation: 1

How to average a calculated value and use group by

I am learning SQL and would like to understand how to do computations on calculated fields(values that are not stored in the database). For example, I used the below query to get the number of days it takes a customer to pay an invoice. This is the difference between the posting date on the invoice(OINV.DocDate) and the posting date on the incoming payment(ORCT.DocDate).

OINV = Invoice header table ORCT = Incoming payment header table RCT2 = Incoming payment lines table

SELECT  OINV.cardcode, OINV.docnum, OINV.DocDate, ORCT.DocDate,
DATEDIFF(dd,OINV.DocDate,ORCT.DocDate) as DaystoPay
FROM OINV
INNER JOIN RCT2 ON OINV.DocNum = RCT2.DocEntry
INNER JOIN ORCT ON RCT2.DocNum = ORCT.docnum
ORDER BY OINV.cardcode

My result looks like this:

cardcode    docnum  DocDate DocDate DaystoPay
C20000  1   00:00.0 00:00.0 1296
C20000  75  00:00.0 00:00.0 1514
C20000  83  00:00.0 00:00.0 1478
C20000  91  00:00.0 00:00.0 1412
C23900  4   00:00.0 00:00.0 1271
C23900  37  00:00.0 00:00.0 1061

Now I want to group this per customer and calculate the average days it takes a customer to pay their invoice i.e...group by OINV.cardcode and then average the DaystoPay values. How can I achieve that with SQL?

The result would look like this:

C20000  5700
C23900  2332

Thanks

Upvotes: 0

Views: 964

Answers (1)

Kit Z. Fox
Kit Z. Fox

Reputation: 674

Your result looks like you want to sum your DaystoPay rather than average. You can do that by using aggregate functions:

SELECT  OINV.cardcode, SUM(DATEDIFF(dd,OINV.DocDate,ORCT.DocDate)) As DaystoPay
FROM OINV
INNER JOIN RCT2 ON OINV.DocNum = RCT2.DocEntry
INNER JOIN ORCT ON RCT2.DocNum = ORCT.docnum
GROUP BY OINV.cardcode    
ORDER BY OINV.cardcode;

If you want to average, then you would probably divide the number of records for each cardcode by the total of the DaystoPay (I'm guessing from your query.)

That would look like this:

SELECT  OINV.cardcode, 
   SUM(DATEDIFF(dd,OINV.DocDate,ORCT.DocDate))/COUNT(OINV.cardcode) As AvgDaysToPay
FROM OINV
INNER JOIN RCT2 ON OINV.DocNum = RCT2.DocEntry
INNER JOIN ORCT ON RCT2.DocNum = ORCT.docnum
GROUP BY OINV.cardcode
ORDER BY OINV.cardcode;

Or more simply:

SELECT  OINV.cardcode, 
   AVG(DATEDIFF(dd,OINV.DocDate,ORCT.DocDate)) As AvgDaysToPay
FROM OINV
INNER JOIN RCT2 ON OINV.DocNum = RCT2.DocEntry
INNER JOIN ORCT ON RCT2.DocNum = ORCT.docnum
GROUP BY OINV.cardcode
ORDER BY OINV.cardcode;

Here is a helpful website for learning more about aggregate functions: http://www.w3schools.com/sql/sql_functions.asp (See comment below)

It's important to note that when you use a Group By clause, you must use aggregate functions on any columns that are not included in the Group By list.

Hope that is useful!

Upvotes: 2

Related Questions