Counting query

Hi,
I have two tables: table 1 contains customer information, and table 2
contains order information.
Table 2 is updated everytime a customer orders some goods. Therefore, a
customer, for example, can appear within table 2 on, say, a total of 5
occasions.
I would like a column in table 1 that tells me how many orders the
corresponding customer has placed in total. Is there anyway of linking
table 1 with table 2 to count the total number of orders a particular
customer has made? (i.e. in the above case 5)
Thanks for your time
Paul Evans
While you can do this it is usually not a good idea. The main reason is
that now you have extra work somewhere (most likely a trigger) to keep that
value up to date and in some cases it can get out of sync. It is usually
better to simply issue a SUM or COUNT against the orders table with a WHERE
clause that filters by customer id. You would normally have an index on the
customer id and the operation would be pretty simple. If you use this value
a lot and there are not a large amount of new rows added to the Orders table
you might consider using an indexed view that sums up by customer. See more
in BOL on Indexed Views.
Andrew J. Kelly SQL MVP
"Paul Evans" <paul_evans1@.btinternet.com> wrote in message
news:uuR3pWS5EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two tables: table 1 contains customer information, and table 2
> contains order information.
> Table 2 is updated everytime a customer orders some goods. Therefore, a
> customer, for example, can appear within table 2 on, say, a total of 5
> occasions.
> I would like a column in table 1 that tells me how many orders the
> corresponding customer has placed in total. Is there anyway of linking
> table 1 with table 2 to count the total number of orders a particular
> customer has made? (i.e. in the above case 5)
> Thanks for your time
> Paul Evans
>