Generating Running Sum of Ratings in SQL

Posted by Koobz on Stack Overflow See other posts from Stack Overflow or by Koobz
Published on 2010-05-25T04:26:29Z Indexed on 2010/05/25 4:31 UTC
Read the original article Hit count: 247

Filed under:

postgresql

|

sum

I have a rating table. It boils down to:

rating_value  created
+2            april 3rd
-5            april 20th

So, every time someone gets rated, I track that rating event in the database.

I want to generate a rating history/time graph where the rating is the sum of all ratings up to that point in time on a graph.

I.E. A person's rating on April 5th might be select sum(rating_value) from ratings where created <= april 5th

The only problem with this approach is I have to run this day by day across the interval I'm interested in. Is there some trick to generating a running total using this sort of data?

Otherwise, I'm thinking the best approach is to create a denormalized "rating history" table alongside the individual ratings.

© Stack Overflow or respective owner

Related posts about postgresql

Postgresql fails to start on Ubuntu 10.04.4 LTS

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
I installed postgresql 9.2 from add-apt-repository ppa:pitti/postgresql using apt-get install postgresql-9.2 At the end of the install and every time I try to launch postgresql by using the following command /etc/init.d/postgresql start or service postgresql start I get this error: Error:… >>> More
can't install psycopg2 in my env on mac os x lion

as seen on Server Fault - Search for 'Server Fault'
I tried install psycopg2 via pip in my virtual env, but got this error: ld: library not found for -lpq (full log here: http://pastebin.com/XdmGyJ4u ) I tried install postgres 9.1 from .dmg and via port, (gksks)iMac-Alexander:~ lorddaedra$ locate libpq /Developer/SDKs/MacOSX10.7.sdk/usr/include/libpq /Developer/SDKs/MacOSX10… >>> More
Postgresql has broken apt-get on Ubuntu

as seen on Super User - Search for 'Super User'
On ubuntu 12.04, whenever I try to install a package using apt-get I'm greeted by: The following packages have unmet dependencies: postgresql-9.1 : Depends: postgresql-client-9.1 but it is not going to be instal led E: Unmet dependencies. Try 'apt-get -f install' with no packages (or specify a so lution)… >>> More
Installing PostgreSQL on FreeBSD (with ports)

as seen on Server Fault - Search for 'Server Fault'
Hey everyone, I am trying to install (using ports) PostgreSQL on a virtual server, running FreeBSD. My one question is this: Which of the following should I install? postgresql-contrib postgresql-docs postgresql-jdbc postgresql-libpgeasy postgresql-libpq++ postgresql-libpqxx postgresql-odbc … >>> More
Strange permission errors in new PostgreSQL installation

as seen on Server Fault - Search for 'Server Fault'
A freshly installed PostgreSQL (with configuration overwritten) won't start: $ sudo service postgresql start * Starting PostgreSQL 9.1 database server * Error: could not read /etc/postgresql/9.1/main/postgresql.conf: Permission denied Looks like it should be able to read it though: $ ls -l postgresql… >>> More

Related posts about sum

Django custom SQL returning single row of results when query returns 2?

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a custom SQL call that is returning different results to the template than I get when I run the same query against the database directly, 1 row vs 2 Query - copied from Django Debug Toolbar: SELECT ((Sum(new_recruit_interviews) / Sum(opportunities_offered)) * 100) as avg_recruit, ((Sum(inspections)… >>> More
PHP Sum Array - sum only elemants of an array

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi all, I have have an array as follows: $row[6] This section of the array has 4 numbers within it that I can display and use without any problems, what I'd like to be able to do is sum the first two numbers and the last two numbers separately. Is there any way to do this? Thanks in advance… >>> More
MySQL how to sum subgroup first then sum total

as seen on Stack Overflow - Search for 'Stack Overflow'
My data in table_1 and table_2. table_1 id_1 num ids_2 3 33 666,777,888 4 44 111,222,333 table_2 id_2 num 111 1 222 2 333 3 666 6 777 7 888 8 I only know how to do what I want with two steps: First LEFT JOIN to get: SELECT 1.id_1, sum(2.num) FROM table_1 AS… >>> More
Reporting Services: Two Tables One Sum

as seen on Stack Overflow - Search for 'Stack Overflow'
My report is as follows: One table provides financial information with sums at the group footer (Grouping is called "StockTable_Shipped"). The group is controlled by a boolean value (1=shows shipped data, 0 = shows received data) The second table is a variance report for data that has been shipped… >>> More
Solving Euler Project Problem Number 1 with Microsoft Axum

as seen on Geeks with Blogs - Search for 'Geeks with Blogs'
Note: The code below applies to version 0.3 of Microsoft Axum. If you are not using this version of Axum, then your code may differ from that shown here. I have just solved Problem 1 of Project Euler using Microsoft Axum. The problem statement is as follows: If we list all the natural numbers… >>> More