Add new column to a table with a value group by value

Question

I have a Circus table as follow

circus_id	circus_date	circus_show_price
1	09-12-2020	78
2	12-01-2021	82

and a Ticket table as follow

ticket_id	circus_id	ticket_category
1	1	Adult
2	1	Student
3	1	Children
4	2	Adult
5	2	Children
6	2	Adult

and i want to alter the circus table by adding a new column called ticket_sold and the value should be as follow

circus_id	circus_date	circus_show_price	ticket_sold
1	09-12-2020	78	3
2	12-01-2021	82	3

this is what I have tried

 alter table circus add ticket_sold numeric(3) default 0;
 update circus set ticket_sold = (select count(ticket_id) from ticket group by circus_id);

it gives me an error said

 single-row subquery returns more than one row

Answer 1

Is is not group by clause you need because query then returns number of tickets per each circus, but - then you get as many rows as there are circus_ids in the ticket table. Instead, correlate subquery to the main table:

update circus c set 
  c.ticket_sold = (select count(t.ticket_id) 
                   from ticket t
                   where t.circus_id = c.circus_id
                  );