Get total of MYSQL table column across multiple records and insert total into new table
Posted on 2012-09-12
I have a customer_registration table that holds records of sometimes multiple products purchased by a customer (by customer_id). Each product entry has it's own service_length (INT) column to hold the number of support sessions for each product.
What, I'd like to do is total the service_length number across a customer_id's multiple records and then INSERT that total along with customer_id into a new table customer_support_sessions - Essentially tying the service_length total to each customer instead of tying it to each product.
I was thinking maybe it could be done with an INSERT INTO statement like below with some kind of a subquery to do the total COUNT for service_length across the multiple customer_id records?
INSERT INTO `customer_support_sessions` (customer_id, service_length)
SELECT cr.customer_id, cr.service_length
FROM customer_registration cr