Counting number of times a value occurs and sending count to another column?
I have a table that I have fed data into through a PHP script, and am managing it using phpMyAdmin. My table has 4 columns. The first is an auto increment, second and third are values being fed in, and the final is meant to keep track of how many times the value from column 3 has appeared.
This is how my table currently appears
RowNumber UserID SongID Plays 1 540 2191 0 2 540 2671 0 3 550 3891 0 4 550 2191 0 5 550 2671 0 6 560 9391 0 7 560 2191 0
I want to search through the whole table and change the value in the Plays column to show how many times the value appears in the table.
Ideally, this is how I want my table to output:
RowNumber UserID SongID Plays 1 540 2191 3 2 540 2671 2 3 550 3891 1 4 550 2191 3 5 550 2671 2 6 560 9391 1 7 560 2191 3
Is there a way to search through the table and update these values? The amount of data being inputted into the table is quite large, so an efficient solution would be greatly appreciated.
Consider using a view instead of a table, unless you need the value cached for performance reasons. You can compute the count of each value in a subquery and join the results back to the table like so:
SELECT Table.RowNumber, Table.UserID, Table.SongID, x.Plays FROM Table INNER JOIN ( SELECT SongID, COUNT(*) AS Plays FROM Table GROUP BY SongID ) x ON Table.SongID = x.SongID;
And create a view from it using CREATE VIEW TableWithPlays AS SELECT .... Having an index on SongID will allow the subquery to complete rather quickly, and you will never have to worry about the Plays column being up to date.
If you do in fact want to cache the values, use an UPDATE query based on the above query:
UPDATE Table a INNER JOIN ( SELECT SongID, COUNT(*) AS Plays FROM Table GROUP BY SongID ) b ON a.SongID = b.SongID SET Plays = b.Plays;
As with the view solution, don't forget the index on SongID.