Animaze
asked on
Get total of MYSQL table column across multiple records and insert total into new table
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
WHERE whatever
--
customer_registration
reg_id
customer_id
product
product_serial
warranty_length
service_length
date_purchased
date_added
customer_support_sessions
sessions_id
customer_id
service_length
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
SELECT cr.customer_id, cr.service_length
FROM customer_registration cr
WHERE whatever
--
customer_registration
reg_id
customer_id
product
product_serial
warranty_length
service_length
date_purchased
date_added
customer_support_sessions
sessions_id
customer_id
service_length
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.