SQL count query based on date-CodePudding

I am trying to perform a count query in mysql to update a column (no_of_orders) in my users table using data from another table (orders).

The table I want to perform a query on is the orders table that has a created_date column in datetime format.

I would like to count the number of orders each user has made in the last 3 months.

I have tried the below and I am getting an error "Operand should only contain one column".

UPDATE users u SET no_of_orders = (
SELECT EXTRACT(month from created_date)>= NOW() - INTERVAL 3 MONTH ,
COUNT(*)
FROM orders o
WHERE o.created_by = u.user_id);

Is there a better way to do this?

Thank you!

CodePudding user response：

LEFT join the table users to a query that aggregates in the table orders:

UPDATE users u 
LEFT JOIN (
  SELECT created_by, COUNT(*) count
  FROM orders
  WHERE created_date >= NOW() - INTERVAL 3 MONTH
  GROUP BY created_by
) o ON o.created_by = u.user_id 
SET u.no_of_orders = COALESCE(o.count, 0);

CodePudding user response：

UPDATE users u SET no_of_orders = (
SELECT COUNT(*)
FROM orders o
WHERE o.created_by = u.user_id AND  EXTRACT(month from o.created_date)>= NOW() - INTERVAL 3 MONTH ) GROUP BY o.created_by;

In sub-query you are returning more than one column that's why the error