1
vote
1answer
34 views

Split two rows into two columns

I have the following table: id | name | action | count ------------------------------ 1 | Value1 | 0 | 27 1 | Value1 | 1 | 49 2 | Value2 | 0 | 7 2 | Value2 | 1 | ...
2
votes
1answer
80 views

Select data based on records created_at column

I have two tables Keywords, ProjectReports: http://sqlfiddle.com/#!15/06ae3/1 As you can see in that example everything works fine, but I want to get more data from those 2 tables and I don't know ...
1
vote
0answers
47 views

Join and aggregate result and insert blanks for missing appendants

Consider these two simplified tables: CREATE TABLE "sources" ( "id" serial NOT NULL, "name" text NOT NULL, CONSTRAINT "sources_pkey" PRIMARY KEY ("id") ); CREATE TABLE "values" ( ...
1
vote
1answer
81 views

SQL hourly data aggregation in postgresql

I am a newbie with database so I'm seeking your help with this one. I have a table containing time series data. 2012/01/01 00:10, 10 2012/01/01 00:30, 5 2012/01/01 01:00, 10 2012/01/01 01:40, 10 ...
3
votes
0answers
135 views

Is there a First() aggregate function in postgresql that returns the exact same type as the input?

Full question re-write I'm looking a First() aggregate function. Here I found something that almost works: CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS anyelement ...
1
vote
2answers
131 views

Returning empty string when string_agg has no records

I am trying to return a text field in a PostgreSQL query that is of the form 'stringOne' || string_agg(field, ',') || 'stringTwo' where for certain elements in the group clause, field is always ...
1
vote
1answer
118 views

Select query for returning different data based on creation time

I have a view, which will Select data from table reports the select will filter the data based on multiple dates. reports table: id, key_id, position, created_at My select: pr.key_id, ...
1
vote
1answer
53 views

Consolidating large table

I need some help with a complex query. Table def: Table "public.agent_log" Column | Type | Modifiers ...
0
votes
2answers
70 views

compare dates in results

I'm looking to compare the dates in the result set Example: SELECT * FROM tbl WHERE foo=bar Output id, datetime, name 1, 2014-01-01 18:13:45, test 1 2, 2014-01-01 19:01:32, test 2 3, 2014-01-01 ...
1
vote
3answers
687 views

Select query to retrieve values even if are null

I need some help in a query. Need to retrieve data from 1 table, even if one of the field has null value. Here's an example: Select name, SUM(credit) as credit From expenses Where name like ...
1
vote
1answer
88 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 ...
1
vote
1answer
108 views

Slow queries related to subqueries using aggregation

Here's a simplified version of the queries I am running: select ... from editions join edition_events on edition_events.edition_id = editions.id join events on events.id = ...
1
vote
1answer
97 views

Get maximum from aggregated values in a query

I have a table details it has a column kills and a column player_slot and a column match_id. I want to find the maximum difference between the SUM of kills for player_slot 0-4 and 128-132. Can someone ...
4
votes
1answer
847 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[] ...
6
votes
3answers
228 views

Is using SUM() twice suboptimal?

I know I have to write SUM twice, if I wish to use it in a HAVING clause (or use a derived table otherwise): SELECT id, sum(hours) AS totalhours FROM mytable GROUP BY id HAVING sum(hours) ...
1
vote
1answer
2k 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 ...
1
vote
0answers
332 views

How to calculate running total based on dynamic values?

I need to calculate the running total in which the target column is changing along with the time. Its something which aws does. Charging based for only what you use. So how do I aggregate sum of ...
3
votes
1answer
112 views

Aggregating statistics from child rows

I have a parent row and then many child rows, and I must aggregate statistics from these child rows. For a more concrete example let's imagine I have one round of golf (parent row) and then child ...
4
votes
1answer
411 views

Is it possible to wrap aggregate functions in Postgres?

Postgres' string_agg(expr, delimiter) function is great. But I would like to have a version that takes a single argument -- the field to aggregate -- and assumes a delimiter of ', ' since that is ...
3
votes
2answers
1k views

postgresql view with max min with id

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 ...
0
votes
0answers
46 views

postgresql view [duplicate]

Possible Duplicate: postgresql view with max min with id I have the following table: CREATE TABLE trans ( id SERIAL PRIMARY KEY, trans_date date, trans_time time ); I ...
7
votes
5answers
11k views

How can I use a default value in a Select query in PostgreSQL?

I would like to use a default value for a column that should be used if no rows is returned. Is that possible in PostgreSQL? How can I do it? Or is there any other way I can solve this? E.g. ...