I was writing a script today to summarize some data and was about to create 4 separate tables, and then insert the data into a new table as a summary. I decided that there had to be a better way.

It came down to creating my summary table and then populating directly from my select statements that were initially going to be used to create one of my 4 data tables.

The following is an example of how to use the MySQL INSERT… SELECT… ON DUPLICATE UPDATE syntax to simplify the aggregation of data. The example assumes that the user_summary table is already created, and `user_date_added` is the primary key on the table.

INSERT INTO user_summary (user_date_added, total_users) 
SELECT user_date_added, COUNT(user_id) as total_users 
FROM user 
GROUP BY user_date_added 
ON DUPLICATE KEY UPDATE total_users = values(total_users);