Counting the number of records

Last Post 20 Apr 2007 01:30 PM by fireflyquilts. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
fireflyquilts
New Member
New Member

--
20 Apr 2007 01:30 PM
I am writing a conversion process. As part of this process I need to have a count of the number of times each customer number appears on various tables in the database. I created a table that contains each customer number for which I am interested in gathering counts. The conversion table contains

conversion cnt table
customer_id customer_addr_cnt payment_cnt loan_cnt ...........(other table counters)
1 null null null
2 null null null

customer_addr table
customer_id addr
1 Lake view lane
2 river way
3 ocean view

payment table loan table
customer_id payment customer_id loan_#
1 10.00 1 1
1 10.00 2 1
1 10.00 2 2
2 20.00 2 3
2 20.00
3 30.00

I would like to end up with the following values on the conversion table

conversion cnt table
customer_id customer_addr_cnt payment_cnt loan_cnt ...........(other table counters)
1 1 3 1
2 1 2 3

Unfortunately with my code i get the total count of all customers on each table that match customers on the conversion table. I am guessing that count is not the appropriate function.

customer_id customer_addr_cnt payment_cnt loan_cnt ...........(other table counters)
1 2 5 4
2 2 5 4


My code is:
upDATE T_CNVRT
SET OBLIG_CNT =
(SELECT COUNT(customer_id)
FROM T_CNVRT A ,T_CUSTOMER_ADDR B,
WHERE a.customer_id = b.customer_id),
payment_CNT =
SELECT COUNT(customer_id)
FROM T_CNVRT A ,T_payment c,
WHERE a.customer_id = c.customer_id),
loan_CNT =
SELECT COUNT(customer_id)
FROM T_CNVRT A ,T_loan d,
WHERE a.customer_id = d.customer_id),


Any ideas?







Acceptable Use Policy
---