[thelist] RecordCount

rudy r937 at interlog.com
Thu Mar 21 10:11:01 CST 2002


> I have several groups which contain documents - i would like
> to count how many documents are contained in each group.

hi paul

instead of retrieving all the rows out of the database and counting them
with your script language, how about asking the database how any there are?

so instead of

>      SELECT [tblGroupData].*, [tblGroups].*
>        FROM [tblGroupData]
>  INNER JOIN [tblGroups]
>          ON [tblGroupData].[Gid] = [tblGroups].[Gid]
>       WHERE [tblGroupData].[Uid] = "& Id &"

just go

       SELECT [tblGroupData].[Gid], count(*) as GroupUidCount
         FROM [tblGroupData]
   INNER JOIN [tblGroups]
           ON [tblGroupData].[Gid] = [tblGroups].[Gid]
        WHERE [tblGroupData].[Uid] = "& Id &"
     GROUP BY [tblGroupData].[Gid]

the number 2 rule in selecting data from a database is that you should not
select columns that you aren't going to use (which is highly probable when
you use "select star" on both tables)

the number 1 rule in selecting data from a database is that you should not
select rows that you aren't going to use (it's much more efficient to let
the database bring back aggregate rows than detail rows)


rudy







More information about the thelist mailing list