I have table named
| order_id | user_id | | 91881 | a001 | | 82191 | a002 | | 73817 | a001 | | 91289 | a003 | | 81828 | a002 | | 82917 | a002 |
I want to create a new table which consist unique user_id and how many orders have been put by the user.
| user_id | total_orders | | a001 | 2 | | a002 | 3 | | a003 | 1 |
For the new table I wrote
CREATE TABLE new_table AS (SELECT DISTINCT user_id FROM orders); ALTER TABLE new_table ADD (total_orders INT)
The problem occurs when I try to update the
total_orders column by write this code
UPDATE new_table SET new_table.total_orders = ( SELECT COUNT(DISTINCT(order_id)) FROM orders GROUP BY user_id ORDER BY user_id ASC) WHERE new.user_id = (SELECT DISTINCT user_id FROM orders);
and results in "Subquery returns more than 1 row"
What should be changed in the update code?
Thanks in advance.