What about using a subselect, Pepak?

SELECT m.key, m.name, m.creation_date, m.note, m.group,
(select SUM(d.amount) from detail d where d.master_key=m.key)
FROM master m
WHERE m.group=1234

This way you get rid of the GROUP BY altogether.

Though I don't know whether this helps with the VIEW problem.

Alternatively, it is possible to order by position, i.e.

GROUP BY 1, 2, 3, 4, 5

Set

PenWin wrote:

> Hi!
>
> I have just noticed something very strange. I have a SELECT which lists all
> records from a master table and a sum from a detail table. Something like:
>
> SELECT m.key, m.name, m.creation_date, m.note, m.group, SUM(d.amount)
> FROM master m
> LEFT JOIN detail d ON d.master_key=m.key
> GROUP BY m.key, m.name, m.creation_date, m.note, m.group
>
> Now in my application I will always select only those rows from master which
> belong to a particular group. Something like:
>
> SELECT m.key, m.name, m.creation_date, m.note, m.group, SUM(d.amount)
> FROM master m
> LEFT JOIN detail d ON d.master_key=m.key
> WHERE m.group=1234
> GROUP BY m.key, m.name, m.creation_date, m.note, m.group
>
> This select performs in about 0.0017 seconds on my system, in other words,
> it is fast enough. To make my code easier to read I wanted to wrap the
> original select in a view and then select from the view:
>
> CREATE VIEW my_view (key, name, creation_date, note, group, total_amount) AS
> [the_select_at_the_top]
>
> SELECT key, name, creation_date, note, group, total_amount
> FROM my_view
> WHERE group=1234
>
> Unfortunatelly, this takes some six seconds to process (about 600x as long
> as the select). It seems likely Firebird first counts the amounts over the
> whole table and then applies the group=1234 condition. My question is, is
> there anything I can do to keep the simplicity of a view but improve the
> speed? The plans look pretty much the same to me:
>
> PLAN SORT (JOIN (MASTER NATURAL,DETAIL INDEX (DETAIL_KEY)))
> PLAN SORT (JOIN (MY_VIEW MASTER NATURAL,MY_VIEW DETAIL INDEX (DETAIL_KEY)))
>
> All this on WinXP, Firebird 1.5 running in SuperServer mode.
>
> And a somewhgat unrelated minor question: Do I really have to list all
> selected fields in the GROUP BY clause? This is one thing I rather like
> about MySQL, that I can simply group by the primary key (m.key) and not type
> the other fields which obviously depend on that primary key...
>
> Thanks,
>
> Pepak

[Non-text portions of this message have been removed]