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

Tasya Fauzia Fitriasari 0
Viewed 32

I want to calculate average customers will order

enter image description here

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

mysql sql
3 Answers

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

Asgar0

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
hsnsd0 
SELECT customerID, (DATEDIFF(dayofyear, MIN(orderDate), MAX(orderDate))/ (COUNT(*) -1)) AS 'date_diff',  
FROM orders
GROUP BY customerID
HAVING COUNT(*) > 1
user8AIn0
Related