?
Solved

Get total of MYSQL table column across multiple records and insert total into new table

Posted on 2012-09-12
1
Medium Priority
?
838 Views
Last Modified: 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
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
0
Comment
Question by:Animaze
1 Comment
 
LVL 58

Accepted Solution

by:
Gary earned 2000 total points
ID: 38391897
What is service_length? Just a number?
If so you can just add sum to that field

INSERT INTO `customer_support_sessions` (customer_id, service_length)
SELECT cr.customer_id, sum(cr.service_length)
FROM customer_registration cr
WHERE whatever
GROUP BY cr.customer_id
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question