At 08:43 PM 12/18/2004 -0600, Claudine Robbins wrote:
After I identify that some rows are duplicated (this is OK for me, this is a detail table), I want to count the number of duplicates for each value and put the resulting value in a column in the same table. How would I go about doing this?
field1 field2 32531 0 32531 0 32531 0 32531 0
I want to update field2 = 4
Claudine,
Create a VIEW which holds both columns/values at any given time!
Here's how:
Based on TransDetail table in ConComp sample database:
CONNECT ConComp IDENTIFIED BY NONE SET ERROR MESSAGE 677 OFF DROP VIEW `TotalItemsInOrder` SET ERROR MESSAGE 677 ON CREATE VIEW `TotalItemsInOrder` (TransID, TotalItems) + AS SELECT TransID, COUNT(*) + FROM TransDetail GROUP BY TransID COMMENT ON VIEW TotalItemsInOrder IS 'Total Items by Order' RETURN
TransID is the actual order number and TotalItems is the total count based on number of line items per order.
That technique will provide you with a VIEW to hold both column values as well as the count.
Hope that helps!
Very Best R:egards,
Razzak.
