Counting occurences of a value in a table

Last Post 20 Apr 2007 04:46 PM by nosepicker. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
fireflyquilts
New Member
New Member

--
20 Apr 2007 02:47 PM
I am writing a conversion process to renumber certain customers on a database. As part of this conversion, I would like to have a count of the occurences of each customer id on each table. i have created a conversion table to contain this information. At this time the converstion table contains

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

I would like to update this table with the number of times each customer_id appears on each table in the database. For example customer_id 1 appears 3 times on loan table and 2 times on the payment table, customer_id 2 appears 1 time on loan table and 1 time on payment table.

loan table payment table
customer_id loan_nbr customer_id payment
1 1 1 10.00
1 2 1 10.00
1 3 2 20.00
2 1
The following code doesn't give me what I want. Any ideas?

upDATE T_CNVRT
SET loan_CNT =
(SELECT COUNT(customer_id)
FROM T_CNVRT a, T_loan 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)

This gives me
customer_id loan_cnt payment_cnt ..........(other table counters)
1 4 3
2 4 3

But this is what I want
customer_id loan_cnt payment_cnt ..........(other table counters)
1 3 2
2 1 1
nosepicker
New Member
New Member

--
20 Apr 2007 04:46 PM
There are probably a few different ways to do this. Here is one:

UPDATE T_CNVRT
SET loan_cnt = loan.loan_cnt, payment_cnt = pymt.payment_cnt
FROM T_CNVRT
LEFT JOIN
(SELECT customer_id, COUNT(*) AS loan_cnt
FROM T_loan
GROUP BY customer_id) AS loan
ON T_CNVRT.customer_id = loan.customer_id
LEFT JOIN
(SELECT customer_id, COUNT(*) AS payment_cnt
FROM T_payment
GROUP BY customer_id) AS pymt
ON T_CNVRT.customer_id = pymt.customer_id
You are not authorized to post a reply.

Acceptable Use Policy