> Sum(1) is like Count(*) in this case, they both work if I list each column
> in SELECT clause and then repeat them all in GROUP BY clause.
>
> There are too many columns in the master table. I would like to use star
> (*). Less typing and safe for future when columns may be added or dropped.
>
> Any other ideas ?
>
The afore mentioned method is the most obvious and should work on all brands
of SQL. Another option is to build a temporary table of the counts and then
join to it.
eg (in SQL Server syntax)
select b.fk, my_count = count(*)
into #tmp
from b
group by b.fk
select a.*, #tmp.my_count
from a
inner join #tmp
on a.pk = #tmp.fk
drop table #tmp
Depending on your brand of SQL you might be able to use a sub select. Eg, in
SQL Server you can do:
select a.*, my_count = (select count(*) from b where b.fk = a.pk)
from a
This might be quite slow though depending on size of tables and whether the
optimizer decides to use you indexes.
David Brennan.
DB Solutions Ltd.
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
