CODEWITHSUNDEEP
sqlpostgresql
TangHongWan
TangHongWan

Reputation: 685

How to sum data in last record in postgresql using SQL

I have a data collection like this.

build   failures
001     1
002     3
003     5

failures column means the failures existed that build.

Is there any way that in SQL to output the data as this (sum all failures before current build)?

build   failures
001     1
002     4
003     9

Upvotes: 0

Views: 92

Answers (2)

Chris Travers
Chris Travers

Reputation: 26454

In general, while @lad2025 is generally correct I would add that for running totals you usually don't want to explicitly specify the ordering. Usually you will get better, more maintainable queries by using the built in window for current row instead.

SELECT build, failures, 
       sum(failures) OVER (ROWS UNBOUNDED PRECEDING) as failures_to_date
  FROM your_tab
 ORDER BY build;

This gives you an ability to re-order your query while keeping the running total being based on whatever ordering you decide (I wouldn't be surprised if optimisations are possible too with this, meaning it might perform better).

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

You could use windowed SUM:

SELECT build, SUM(failures) OVER(ORDER BY build) AS failures
FROM your_tab
ORDER BY build;

Upvotes: 1

Related Questions