Group By Parent Columns

Where possible, use GROUP BY with columns of a parent table instead of a child table.

Background

First have a look at the example. In this example, you cannot avoid using HAVING. The RDBMS needs to join the tables before it can decide which scheduled tours have a total price to be paid of more than 1000. The result is the same whether you group by the primary key of the parent table or the foreign key of the child table. As a rule, always group by the primary key of the parent table. This is faster both at Rules Engine and RDBMS levels. Of course, this also holds for statements with subqueries.

Example

In general, use SQL statements like the following:

For each scheduled tour, if the total price to be paid is more than 1000, select the total price to be paid for all reservations.

SELECT      st.schedtour_id

,           SUM(r.price)

FROM        schedtour st

,           reservation r

WHERE       st.schedtour_id=r.schedtour_id

GROUP BY    st.schedtour_id

HAVING      SUM( r.price ) >= 1000