All Questions

Filter by
Sorted by
Tagged with
1
vote
1answer
60 views

Using Window Function instead of Select sub-query for counting

I have to count the number of Orders a table, per Employee, for two criteria - "Late Orders". "Total Orders" The query includes Orders table and Orders table. They are joined together via the ...
0
votes
2answers
29 views

sum only different id's values with any aggreagte function in postgresql

I need your help. Let's assume that we have a table like below +---------------+-------+--------+----------+ | barcode | shop | id | entrance | +---------------+-------+--------+----------+...
1
vote
2answers
105 views

Strip longest common suffix and concat in Postgres

Given a table t: id | name ------------ 1 | abcfug 1 | deffug 1 | hijfug 2 | etc How can I do something like: select string_agg(strip_lcs(name), ', ') from t where id = 1 returning: abc, def, ...
5
votes
1answer
227 views

If at least a negative number, sum; otherwise just show

I have a need to sum values together only if there is a negative value present in a partition. If there are no negative values in a partition, it should just output the row. Here's what I have right ...
1
vote
1answer
19 views

Finding average of multiple delta times

I'm looking to find the average interval between successive rows across the whole query - delta-previous in below example: SELECT *, "RecognitionTimestamp" - lag("RecognitionTimestamp") OVER(ORDER ...
4
votes
1answer
490 views

Select last non-null value for given partition - Postgres 10

I have a table with the following structure: date city cntry free occ 2018-08-13 16:30:00 2 12 5 2018-08-13 16:30:00 2 12 0 2018-08-13 14:30:00 2 12 1 ...
0
votes
1answer
207 views

OLAP window functions in DB2

I am using DB2 z/OS 11.1.5. According to this reference, OLAP functions such as first_value should be supported at least in DB2 9.7. However, when trying to execute the query declare global ...
1
vote
2answers
18k views

Sum of previous n number of columns based on some category

I have following input for which I need to calculate the sum of values for previous x number of weeks for each category. If the x is 3 the output would look like this: Note that the last value is 49 ...
2
votes
4answers
2k views

Set non-negative floor for rolling sum, in PostgreSQL

This is a really fun question (asked for SQL Server) and I wanted to try it out to see how it was done in PostgreSQL. Let's see if anyone else can do it better. Taking this data, CREATE TABLE foo AS ...
2
votes
2answers
2k views

Nested aggregate functions over different dimensions

I have a table where I periodically record the throughput of all network interfaces on a set of servers: create table net ( ts timestamptz not null, host text not null, interface text not ...
4
votes
2answers
1k views

T-SQL to Group time interval change by date range in sql server

The original table has the column Timestamp with Interval Interval: difference in minute between the current and previous Timestamp when sorted by Timestamp itself Timestamp Interval(...
0
votes
1answer
193 views

Why can't you do basic operations AFTER an aggregate in a window function? Like (max(x)+5), vs (5+max(x))

The syntax for aggregates and window functions is confusing, this does not work, SELECT (max(x)+5) OVER () FROM generate_series(1,10) AS t(x); While this works SELECT 5+max(x) OVER () FROM ...
1
vote
1answer
241 views

Postgresql 9.4 incrementing value issue

I am trying to go from the first table: test4 (pid, event, time) and count how many events transition to another (in second table eventtransitions). For instance, in the final table, there should be ...
3
votes
1answer
103 views

Counting entites based on status in rolling interval

Given the following table: CREATE TABLE status_log ( entity_id bigint NOT NULL, user_id bigint NOT NULL, status character varying(128), "timestamp" timestamp(6) without time zone ) I want to ...
2
votes
1answer
74 views

Postgres - Querying Min, Max and getting associated columns data specific to those result of those rows

I have query in which returns averaged values by site and month. What I want to know is the min and max of those monthly averages by site and (the difficult part) the month when each occurred. Here ...
3
votes
2answers
6k views

PostgreSQL ntile() partition

i have a point grid with population values. each point has an id and population value. i also have an state_id which say in what state the point is. now i want to calculate the percentile ntile(100) ...
7
votes
2answers
392 views

Average of last 4 runs of a product

I need to show the average weight of a product over its last 4 production runs. I'm not sure how best to describe it other than an example: Let's imagine I have the following table that lists a ...
6
votes
3answers
13k views

Calculate rolling sum over 7 consecutive day period on PostgreSQL

I need to get the rolling sum over a period of 7 days for each row (1 row per day). For example: | Date | Count | 7-Day Rolling Sum | ------------------------------------------ | 2016-02-01 | ...
3
votes
1answer
662 views

Add a total quantity column for groups of rows

I have an Items table and an Inventory table. An Item can belong to multiple Inventory records. I'm trying to return a list of ALL Inventory records but with their quantity in a new column. For ...
1
vote
1answer
55 views

Extract start and end per group of rows, where only the end can be identified

I have a table that looks like this. id date_from date_to description vehicle 1 2015-10-21 08:00 2015-10-21 10:00 GARAGE TO CLIENT 1 1 2 2015-10-21 12:00 ...
2
votes
2answers
3k views

Calculating Running Average using Over clause in SQL server

My table looks like this: CREATE TABLE [dbo].[TimeSeries]( [LOCID] [int] NOT NULL, [Date] DateTime, [YEAR] int, [MONTH] tinyint, [RTT] [int] PRIMARY KEY ( [LOCID] ASC,[Date] ASC) ) It contains 12 ...
1
vote
2answers
37 views

Selecting Values That Differ From Section Average

I'm attempting to select data from a table that's already been made containing records of file size as taken every twelve hours. The table looks something like this: surrogate_id | partition_id | ...
3
votes
1answer
232 views

Extend a single week's win/loss ratio calculation to a whole season

I'm quite inexperienced at SQL, and I'm trying to improve a query in a small project. The database schema looks somewhat like this. (I've reduced to relevant information, if there seems to be ...
0
votes
1answer
5k views

Select on multiple tables with group by

Using Postgres 9.3, I have problem with a select over few tables. I have simcards stock and I would like to get something like this: | network | supplier | total topups (EUR) | 1st topup qty| | ...
4
votes
1answer
1k views

Grouping() equivalent in PostgreSQL?

I have a SQL query that runs in MS SQL Server and it has grouping(), rank() and rollup() functions / keywords in it. The query is given below. How can this be translated to PostgreSQL? select top ...
5
votes
4answers
20k views

Return total number of rows and selected (aggregated) data

I have a function which is selecting some data from a table. I would like to return the selected data and the total number of rows in that table. How can I do that or how can I get the same result in ...
1
vote
1answer
410 views

Retrieve title from rows with a maximum count in other columns

Given the following table archived: CREATE TABLE archived ( sid serial -- row id showurl text -- can be: btg, tmwwt, gcaf, emda title text reference text -- title id ...
11
votes
1answer
11k views

How do I get the aggregate of a window function in Postgres?

I have a table containing a two columns of permutations/combinations of integer arrays, and a third column containing a value, like so: CREATE TABLE foo ( perm integer[] NOT NULL, combo integer[] ...
11
votes
4answers
4k views

How can I write windowing query which sums a column to create discrete buckets?

I have a table which includes a column of decimal values, such as this: id value size -- ----- ---- 1 100 .02 2 99 .38 3 98 .13 4 97 .35 5 96 .15 6 95 .57 7 94 .25 ...
2
votes
1answer
10k views

Select first row (grouping) + add aggregate function

First have a look at this question on StackOverflow. I'm looking to accomplish the same task, except I also need to add an aggregate function (PostGIS's ST_Union) to my query. How can I combine the ...
7
votes
2answers
12k views

PostgreSQL query with max and min date plus associated id per row

I have the following table: CREATE TABLE trans ( id SERIAL PRIMARY KEY, trans_date date, trans_time time ); I want to have the following view CREATE OR REPLACE VIEW daily_trans ...