CODEWITHSUNDEEP
sql
DatWunGuy102
DatWunGuy102

Reputation: 275

How to query the number of changes that have been made to a particular column in SQL

I have a database with a column that I want to query the amount of times it has changed over a period of time. For example, I have the username, user's level, and date. How do I query this database to see the number of times the user's level has changed over x amount of years?

(I've looked in other posts on stackoverflow, and they're telling me to use triggers. But in my situation, I want to query the database for the number of changes that has been made. If my question can't be answered, please tell me what other columns might I need to look into to figure this out. Am I supposed to use Lag for this? )

Upvotes: 0

Views: 675

Answers (3)

bfavaretto
bfavaretto

Reputation: 71908

In case you are already keeping track of the level history by adding a new row with a different level and date every time a user changes level:

SELECT username, COUNT(date) - 1 AS changes
FROM table_name
WHERE date >= '2011-01-01'
GROUP BY username

That will give you the number of changes since Jan 1, 2011. Note that I'm subtracting 1 from the COUNT. That's because a user with a single row on your table has never changed levels, that row represents the user's initial level.

Upvotes: 0

Ionut Flavius Pogacian
Ionut Flavius Pogacian

Reputation: 4801

Have you ever heard of the LOG term ? You have to create a new table, in wich you will store your wanted changes. I can imagine this solution for the table:

  • id - int, primary key, auto increment
  • table - the table name where the info has been changed
  • table_id - the information unique id from the table where changes have been made
  • year - integer
  • month - integer
  • day - integer

knowin this, you can count everything

Upvotes: 1

jjathman
jjathman

Reputation: 12604

A database will not inherently capture this information for you. Two suggestions would be to either store your data as a time series so instead of updating the value you add a new row to a table as the new current value and expire the old value. The other alternative would be to just add a new column for tracking the number of updates to the column you care about. This could be done in code or in a trigger.

Upvotes: 2

Related Questions