SQL count query based on date

Question

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!

You need to look into GROUP BY. That won't solve your problem, but may help. — Barry Carter
– Barry Carter, Commented Jul 14, 2022 at 14:39
The update is not correct. Move the extract part in the where clause and put same sub-query outside with where exists clause too. — Pankaj
– Pankaj, Commented Jul 14, 2022 at 14:39
The error comes from the select clause, you select 2 data and the update expect only 1 data. You should separate the count and extract. — LPK
– LPK, Commented Jul 14, 2022 at 14:40

forpas · Accepted Answer · 2022-07-14 14:55:30Z

1

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);

answered Jul 14, 2022 at 14:55

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

lavender1 Over a year ago

hi, thank you but this returns the total number of orders, not ones made in the last 3 months

forpas Over a year ago

@lavender1 dis you miss WHERE created_date >= NOW() - INTERVAL 3 MONTH ?

lavender1 Over a year ago

No, I copied your answer exactly

forpas Over a year ago

@lavender1 then either you are doing something wrong or created_date is not a valid datetime.

devesh · Accepted Answer · 2022-07-14 15:08:04Z

0

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

edited Jul 14, 2022 at 15:08

answered Jul 14, 2022 at 14:52

devesh

6436 silver badges28 bronze badges

Collectives™ on Stack Overflow

SQL count query based on date

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related