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.

Answers


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.


Need Your Help

Jquery count div elements by class from list value

jquery class count elements

Hello i want to count all visible div Elements with the value form the list item.

How to generate a sql from the inputs to the method

java sql database

I have my below method which will accept two parameters-

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.