Operand data type date is invalid for subtract operator when calculate average date-CodePudding

I want to calculate average customers will order

but when i calculate there's have a problem, mine is:

SELECT CustomerID, (MAX(SalesDate) - MIN(SalesDate)) / (COUNT(*) - 1)
  FROM TrSalesHeader
 GROUP BY CustomerID
HAVING COUNT(*) > 1

and i've message "Operand data type date is invalid for subtract operator."

can anyone help me to solve this problem? thankyou

CodePudding user response：

You seem to have basic syntax error. Use Count(*)

SELECT 
  CustomerID,
  (MAX(SalesDate) - MIN(SalesDate) / (COUNT(*) - 1) )
FROM
  TrSalesHeader 
GROUP BY CustomerID 
HAVING COUNT(*) > 1

Since you have not mentioned your desired OP. This is only what I can get upto

CodePudding user response：

I assume you calculate average by calculating the difference in the max/min dates and dividing by total records. Use the default function to do this instead of subtracting directly

SELECT CustomerID, DATEDIFF(MAX(SalesDate),MIN(SalesDate)) / (COUNT(*) - 1)
  FROM TrSalesHeader
 GROUP BY CustomerID
HAVING COUNT(*) > 1