Mysql Question posted in
The official MySQL documentation can be found here.

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

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

Tags:

3

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
  3. 0 
    SELECT customerID, (DATEDIFF(dayofyear, MIN(orderDate), MAX(orderDate))/ (COUNT(*) -1)) AS 'date_diff',  
FROM orders
GROUP BY customerID
HAVING COUNT(*) > 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search