Combine multiple rows in a MySQL table

Question

I have a MySQL database table that logs site searches. If it encounters a new search phrase it inserts a new row with the phrase and a counter value of 1. If the phrase already exists it increments the counter.

For example:

Id   term       count

1    boots      14
2    shirts     2031
3    t-shirt    1005
4    tshirt     188

Unfortunately the table contains duplicates for many phrases, for example:

Id   term       count

12   sneakers   711
26   sneakers   235
27   sneakers   114
108  sneakers   7

What would a MySQL query look like that combines all the duplicates into one row, totaling up their counts?

What I want:

Id   term       count

12   sneakers   1067

Thank you in advance.

LSerni · Accepted Answer · 2020-05-13 22:06:25Z

You are looking for the SUM() aggregate function:

 -- `count` is a reserved word so must be enclosed in backticks
 SELECT MIN(Id) AS Id, term, SUM(`count`) AS `count` FROM tablename GROUP BY term;

In this case however, if this result is what you want on a permanent basis, I would do as follows:

-- Create a table identical to the current one
CREATE TABLE newtable (Id integer not null auto_increment, term ...)
-- Create a UNIQUE index on the term column
CREATE UNIQUE INDEX newtable_term_udx ON newtable(term);
-- Populate the table with the cleaned results
INSERT INTO newtable (term, `count`) SELECT term, SUM(`count`) ...as above
-- rename the old table as backup, rename newtable with the same name as old table

Then whenever you do an INSERT into the table (which is now the new table) do

INSERT INTO tablename (term, `count`) VALUES ('new word', 1)
    ON DUPLICATE KEY UPDATE `count`=`count`+1

This way, if the new term does not exist it will be created with an initial count of 1, and if it does exist, its count will be incremented by 1, all automatically.

Kurt Kline · Accepted Answer · 2020-05-13 22:09:38Z

1

select min(Id) as Id, term, sum(count) as count
from your_table
group by term;

Alternatively, you can use max(Id), depending on if you want the first or last Id value for each term.

You can play around with the query here: db-fiddle

edited May 13, 2020 at 22:09

answered May 13, 2020 at 21:59

Kurt Kline

2,1191 gold badge15 silver badges24 bronze badges

Collectives™ on Stack Overflow

Combine multiple rows in a MySQL table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related