Group By Parent Columns

See Also

Tip

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