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
15
votes
2 answers

How to filter jsonb array elements

I have a table with a jsonb column which contains an array of objects: create table my_table( id int primary key, phones jsonb); The objects consist of phone numbers and statuses. insert into my_table values (1, '[ { "phone":…
lalol
  • 163
  • 1
  • 6
15
votes
3 answers

Aggregate List of objects in Java

Do we have any aggregator function in Java to perform the below aggregation? Person { String name; String subject; String department; Long mark1; Long mark2; Long mark3; } List contains data as below. Name |Subject …
Swadeesh
  • 576
  • 1
  • 4
  • 23
15
votes
7 answers

Is there a better way to calculate the median (not average)

Suppose I have the following table definition: CREATE TABLE x (i serial primary key, value integer not null); I want to calculate the MEDIAN of value (not the AVG). The median is a value that divides the set in two subsets containing the same…
Ghislain Leveque
  • 958
  • 1
  • 8
  • 21
15
votes
2 answers

MODE aggregation function with redshift

I have the following tables: customers customer_id name ---------------- 1 bob 2 alice 3 tim purchases id customer_id item_bought -------------------------- 1 1 hat 2 1 shoes 3 2 …
Benjamin Crouzier
  • 40,265
  • 44
  • 171
  • 236
15
votes
2 answers

How to translate the PostgreSQL array_agg function to SQLite?

This query works in PostgreSQL: Select ot.MCode,array_to_string(array_agg(tk1.TName || ',' || ot.TTime), ' - ') as oujyu_name_list From TR_A ot inner join MS_B tk1 on ot.Code = tk1.Code Where ot.Code in (Select Code From TR_C )…
D T
  • 3,522
  • 7
  • 45
  • 89
15
votes
2 answers

MYSQL CONCAT MAX LENGTH

Following this post: POST ABOUT CONCAT My problem is that i have many rows CONCAT into one row. For example if i have 10 rows with string around 50 chars, my query will show me only 6-7 of that rows or something like that. I searech in stack and…
Crackeraki
  • 263
  • 2
  • 4
  • 9
15
votes
4 answers

SQL - Subquery in Aggregate Function

I'm using the northwind database to refresh my SQL skills by creating some more or less complex queries. Unfortunately I could not find a solution for my last use case: "Get the sum of the five greatest orders for every category in year 1997." The…
Thomas
  • 232
  • 1
  • 2
  • 8
15
votes
4 answers

Aggregate functions over arrays

I have a table like this: +-----+----------------+ | ID | array300 | +-----+----------------+ | 100 | {110,25,53,..} | | 101 | {56,75,59,...} | | 102 | {65,93,82,...} | | 103 | {75,70,80,...} | +-----+----------------+ array300 column is an…
f.ashouri
  • 5,409
  • 13
  • 44
  • 52
15
votes
3 answers

What can an aggregate function do in the ORDER BY clause?

Lets say I have a plant table: id fruit 1 banana 2 apple 3 orange I can do these SELECT * FROM plant ORDER BY id; SELECT * FROM plant ORDER BY fruit DESC; which does the obvious thing. But I was bitten by this, what does this do? SELECT * FROM…
nawfal
  • 70,104
  • 56
  • 326
  • 368
14
votes
6 answers

Missing 'Median' Aggregate Function in Django?

The Development version of Django has aggregate functions like Avg, Count, Max, Min, StdDev, Sum, and Variance (link text). Is there a reason Median is missing from the list? Implementing one seems like it would be easy. Am I missing something? How…
sutee
  • 12,568
  • 13
  • 49
  • 61
14
votes
3 answers

SQL-style GROUP BY aggregate functions in jq (COUNT, SUM and etc)

Similar questions asked here before: Count items for a single key: jq count the number of items in json by a specific key Calculate the sum of object values: How do I sum the values in an array of maps in jq? Question How to emulate the COUNT…
Onkeltem
  • 1,889
  • 1
  • 18
  • 27
14
votes
9 answers

How to aggregate (counting distinct items) over a sliding window in SQL Server?

I am currently using this query (in SQL Server) to count the number of unique item each day: SELECT Date, COUNT(DISTINCT item) FROM myTable GROUP BY Date ORDER BY Date How can I transform this to get for each date the number of unique item over…
RockScience
  • 17,932
  • 26
  • 89
  • 125
14
votes
1 answer

Custom aggregation on PySpark dataframes

I have a PySpark DataFrame with one column as one hot encoded vectors. I want to aggregate the different one hot encoded vectors by vector addition after groupby e.g. df[userid,action] Row1: ["1234","[1,0,0]] Row2: ["1234", [0 1 0]] I want the…
user2242666
  • 211
  • 1
  • 2
  • 7
14
votes
3 answers

Pairwise array sum aggregate function?

I have a table with arrays as one column, and I want to sum the array elements together: > create table regres(a int[] not null); > insert into regres values ('{1,2,3}'), ('{9, 12, 13}'); > select * from regres; a ----------- {1,2,3} …
François Beausoleil
  • 16,265
  • 11
  • 67
  • 90
14
votes
1 answer

Aggregate strings in descending order in a PostgreSQL query

In addition to the question How to concatenate strings of a string field in a PostgreSQL 'group by' query? How can I sort employee in descending order? I am using PostgreSQL 8.4 which doesn't support string_agg(). I've tried to use the following,…
David
  • 143
  • 1
  • 4
1 2 3
…
99 100