Stack Overflow
Stack Overflow

Questions tagged [aggregate-functions]

Aggregate functions are a subset of SQL functions that compute a single value from multiple input rows, mostly used in `SELECT` queries with a `GROUP BY` clause. Practically all modern RDBMS feature aggregate functions. Typical examples include `COUNT()`, `SUM()`, `MIN()`, `MAX()`, and `AVG()`.

Aggregate functions are a subset of SQL functions that compute a single value from multiple input rows, mostly used in SELECT queries with a GROUP BY clause. Practically all modern RDBMS feature aggregate functions.

Typical examples include COUNT(), SUM(), MIN(), MAX(), and AVG().

5778 questions
2
votes
1 answer

optimizing postgres view for timestamps and aggregation of fields from another table

I've greatly simplified the examples to hopefully produce a clear enough question that can be answered: Consider a table of events CREATE TABLE alertable_events ( unique_id text NOT NULL DEFAULT ''::text, generated_on timestamp without time zone…
Reed Debaets
  • 482
  • 1
  • 6
  • 18
2
votes
1 answer

How do I calculate percent of whole within a Postgres JSON array?

I've got several million rows in a PostgreSQL 9.3 table, and I'm struggling to calculate aggregate statistics. Here are the columns: object_id | date | column_with_json_data_in_key-value_format | bunch_of_irrelevant_columns There is one row for…
Jeff Widman
  • 22,014
  • 12
  • 72
  • 88
2
votes
2 answers

How to group by a, b and return set of N rows of b

Using Postgres 9.3.2, I want to get a count of req_status grouped by req_time and customer_id and return a set of n rows for each customer_id, even when req_status count is zero. req_time req_id customer_id …
McKibet
  • 633
  • 1
  • 5
  • 12
2
votes
1 answer

SQL query over parents with exact same set of children

I have parent-child relation in my table (simplified version listed below): | parent | child | |--------+-------| | p1 | c1 | | p1 | c2 | |--------+-------| | p2 | c1 | | p2 | c2 | |--------+-------| | p3 | c1 …
ilardm
  • 146
  • 8
2
votes
1 answer

SQL Server 2008: Inserting incomplete data and updating it

Handling Missing Data, Incomplete data is an issue faced by all Data Miners/ Developers (depending on job function) in the industry. If I want to convert hourly data (24 records) to daily (1 record), what are the best practices to achieve this. use…
half-baked prgrmr
  • 45
  • 8
2
votes
0 answers

multiple joins and aggregate function

i have recently consolidated few database tables into one and made a better db design. i am about to adjust all the needed sql scripts which were based on the old table. my new db scheme looks like this: So basically i have a table for each player…
dom
  • 652
  • 1
  • 16
  • 35
2
votes
3 answers

Update using a subquery with aggregates and groupby in Postgres

I'm trying to update a column in a table with the max value of that column grouped by another column. So for example, say we have a table named transactions with two columns: quantity and item_name. And for whatever reason we want to set quantity…
Gus
  • 241
  • 1
  • 6
  • 17
2
votes
1 answer

How to update a table using another table or aggregation results from subquery?

Firstly, I'm using MonetDB branch from MonetDB Database Server Toolkit v1.1 (Feb2013-SP1) I have two tables, and want to complete the following two functions without creating a temporary table: 1) update one table from another table; and 2) update…
Ning
  • 461
  • 4
  • 9
2
votes
2 answers

aggregate functions (count, sum, ...) broken (Jaspersoft Studio 5.5.0.final and 5.5.1.final)?

(I added the basic problem solution and the underlying understanding in the bottom part of my answer (marked by: 1), underneath the two example report graphics!) All I actually like to have is a DISTINCT COUNT on a String column field, but it seems…
Andreas Covidiot
  • 4,286
  • 5
  • 51
  • 96
2
votes
2 answers

Custom aggregate function in PostgreSQL

Is it possible to write an aggregate function in PostgreSQL that will calculate a delta value, by substracting the initial (last value in the column) from the current(first value in column) ? It would apply on a structure like this rankings…
maephisto
  • 4,952
  • 11
  • 53
  • 73
2
votes
2 answers

Group and count events per time intervals, plus running total

I'm a fairly new Postgres user, I'm sure there's an answer to this already but I can't find it. I need to analyze some data in an activity log table, grouping the the results by time period. A simple version of the problem would be a table with…
PerryW
  • 1,426
  • 1
  • 15
  • 25
2
votes
1 answer

ElasticSearch aggregation function

Is that a possible to define an aggregation function in elastic search? E.g. for data: author weekday status me monday ok me tuesday ok me moday bad I want to get an aggregation based on author and weekday, and as a value I want to…
Mikl X
  • 1,199
  • 11
  • 17
2
votes
1 answer

How to create an aggregate function to return the last n records?

I'm using Postgres and would like to create an aggregate function to return the last n most recent records (according to another column). Is this possible? If so, how can I do this? Or would it be better to not do this? Looking for something like…
Bradford
  • 4,143
  • 2
  • 34
  • 44
2
votes
0 answers

Return multiple rows from a Postgres aggregate function?

Is it possible for a Postgres aggregate function to return multiple rows? For example, in the same way that regexp_matches will return multiple rows when the g flag is passed, is I'd like to create an aggregate function which returns a histogram…
David Wolever
  • 148,955
  • 89
  • 346
  • 502
2
votes
2 answers

MySql MAX query optimization

Given this situation, I have two tables with the following existing indices: CREATE TABLE tblBusses ( intID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, strBus VARCHAR(100) NOT NULL DEFAULT "" ); CREATE INDEX indBussesBus ON…
Florian Mertens
  • 2,418
  • 4
  • 27
  • 37
1 2 3
…
99 100