Counting number of times customer appears in other tables

[lakekids]

I have a customer count table that contains customer number as primary key, customer_cnt, loan_cnt, payment_cnt, etc.
At this point the only data in the table is the customer number.
Customer number is also part of the key on other tables in the database. What I want to do is update this customer count table with the number of times that customer number appears in the other tables in the database. I have tried the following statement but what it does is update every customer number on the customer count table with the total count for all customers. For example, there are 2 customer numbers in the customer count table, and these 2 customer numbers are found 1 time each on the customer address table. When it updates the customer count table it shows the customer count value for both customers as 2. What I really want is for the customer count to be 1(each customer is represented only once on the customer address table) and then the number of times that particular customer number appears on each of the other associated tables.

Customer cnt table contains
customer # customer_addr_cnt payment_cnt
1 null null
2 null null

Customer address table contains
Customer #
1
2
3
4
5

payment table contains
customer # payment
1 $10
1 $20
1 $10
2 $40
3 $50

What I would like to see in the customer cnt table is

Customer # customer_address_cnt payment_cnt
1 1 3
2 1 1


Instead what I get is
Customer # customer_address_cnt payment_cnt
1 2 4
2 2 4



My code

Update t_customer_cnt
SET customer_addr_CNT =
(SELECT COUNT(a.customer_NBR)
FROM T_customer_cnt b,T_customer a
WHERE b.customer_NBR= a.customer_NBR),
payment_CNT =
(SELECT COUNT(a.customer_NBR)
FROM T_customer_cnt b,T_payment a
WHERE b.customer_NBR= a.customer_NBR)

any ideas?

 

[bborissov]

Try:

[COLOR=blue]UPDATE[/color] Customer
       [COLOR=blue]SET[/color] Customer_Addr_Cnt = Tbl1.Cnt,
       [COLOR=blue]SET[/color] Payment_Cnt       = Tbl2.Cnt
[COLOR=blue]FROM[/color] t_customer_cnt Customer
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] customer_NBR, [COLOR=#FF00FF]COUNT[/color](*) [COLOR=blue]AS[/color] Cnt
                   [COLOR=blue]FROM[/color] T_Customer
                   [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] .customer_NBR) Tbl1
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] customer_NBR, [COLOR=#FF00FF]COUNT[/color](*) [COLOR=blue]AS[/color] Cnt
                   [COLOR=blue]FROM[/color] T_Payment
                   [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] .customer_NBR) Tbl2

BUT keep in mind that this code will update only these customers which are presented in BOTH tables. If you want to update ALL customers change INNER to LEFT JOIN.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[monksnake]

A simple way to set it up would be like this, since you only have 2 tables to count up, simply take a count from each table where customer# = the current t_customer_cnt customer# and add the 2 numbers together.

update t_customer_cnt
set customer_addr_CNT =
(select count(*) from t_customer where customer# = t_customer_cnt.customer#) + (select count(*) from t_payment where customer# = t_customer_cnt.customer#)

<.

 

[bborissov]

Oops, Copy and paste problem, change:

  [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] .customer_NBR)

to

  [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] customer_NBR)

In both derived tables.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[RobertC22]

Using some Microsoft features that are not part of ANSI SQL:


Update b
SET
customer_addr_CNT =
(SELECT COUNT(a.customer_NBR)
FROM T_customer a
WHERE b.customer_NBR= a.customer_NBR),
payment_CNT =
(SELECT COUNT(a.customer_NBR)
FROM T_payment a
WHERE b.customer_NBR= a.customer_NBR)
from t_customer_cnt b

------
Robert