sum - SQL Query to count number of tickets reserved

Question

I have two tables:

Table 1:

id 
title

Table 2:

id 
Table_1_id
number_of_tickets

I need a query to count the number of tickets from table 2 and show: Table_1_id, title, total_number_of_tickets

I wrote this code but it gives me the error:

"column "plays.title" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2: SELECT reservations.play_id AS id , plays.title , SUM(number..."

SELECT reservations.play_id AS id , plays.title , SUM(number_of_tickets) AS reserved_tickets FROM plays
INNER JOIN reservations
ON plays.id = reservations.play_id
GROUP BY play_id
ORDER BY SUM(number_of_tickets)

Answer 1

You could use a subquery:

select t1.*,
    (select coalesce(sum(number_of_tickets), 0) from t2 where t2.table_1_id = t1.id) as number_of_tickets
from t1

With an index on t2(table_1_id, number_of_tickets), this should be an efficient option.

---

As for your original code (that was added as an edit to the question). The intent is OK, the problem is with the GROUP BY clause, that is not consistent with the SELECT clause. In MySQL, you could write this as:

SELECT p.id , p.title, SUM(r.number_of_tickets) AS reserved_tickets 
FROM plays p
INNER JOIN reservations r ON p.id = r.play_id
GROUP BY p.id
ORDER BY reserved_tickets

All non-aggregated columns in the SELECT clause come from table plays, and we group by plays.id, wich presumably is the primary key of that table.

Note that I modified the query to use table aliases: this is good practice, that makes the queries easier to write and read. Also note that column aliases are allowed in the ORDER BY clause.