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
21
votes
1 answer

Flatten aggregated key/value pairs from a JSONB field?

I am working in Postgres 9.4 with the following table: Column │ Type │ Modifiers ─────────────────┼──────────────────────┼────────────────────── id │ integer │ not null default practice_id │…
Richard
  • 62,943
  • 126
  • 334
  • 542
21
votes
5 answers

Is it possible to perform a bitwise group function?

I have a field in a table which contains bitwise flags. Let's say for the sake of example there are three flags: 4 => read, 2 => write, 1 => execute and the table looks like this*: user_id | file | …
nickf
  • 537,072
  • 198
  • 649
  • 721
20
votes
2 answers

Sum totals of two queries

I have two basic queries which I need to sum the totals of: Select hours, sum(hours) FROM table WHERE name='xxx' and Description='Worked' Select hours2, sum(hours2) FROM table WHERE name='xxx' and Description2='Worked' I've tried UNION and it will…
This Guy
  • 203
  • 1
  • 2
  • 5
20
votes
1 answer

Oracle / SQL - Count number of occurrences of values in a single column

Okay, I probably could have come up with a better title, but wasn't sure how to word it so let me explain. Say I have a table with the column 'CODE'. Each record in my table will have either 'A', 'B', or 'C' as it's value in the 'CODE' column. …
dscl
  • 1,616
  • 7
  • 28
  • 48
20
votes
6 answers

How to set correct attribute names to a json aggregated result with GROUP BY clause?

I have a table temp defined like this: id | name | body | group_id ------------------------------- 1 | test_1 | body_1 | 1 2 | test_2 | body_2 | 1 3 | test_3 | body_3 | 2 4 | test_4 | body_4 | 2 I would like to produce a result grouped by…
Przemek
  • 6,300
  • 12
  • 44
  • 61
20
votes
3 answers

Return true if all column values are true

Is there a faster way in PostgreSQL to essentially do an if on several rows? Say I have a table ticket | row | archived 1 | 1 | true 1 | 2 | true 1 | 3 | true 2 | 1 | false 2 | 2 | true Is there any way I could do…
mouckatron
  • 1,289
  • 2
  • 13
  • 23
19
votes
4 answers

Multiple aggregate functions in one SQL query from the same table using different conditions

I'm working on creating a SQL query that will pull records from a table based on the value of two aggregate functions. These aggregate functions are pulling data from the same table, but with different filter conditions. The problem that I run…
Eric Ness
  • 10,119
  • 15
  • 48
  • 51
19
votes
7 answers

How to use MAX() on a subquery result?

I am new to Oracle and the SQL world. I have a slight issue with a query that I cannot figure out for the life of me, I have spent a few hours trying different approaches and I cannot get the result I expect. So heres my query: SELECT * from(Select…
user1916441
  • 211
  • 1
  • 2
  • 4
19
votes
5 answers

Group/Count list of dictionaries based on value

I've got a list of Tokens which looks something like: [{ Value: "Blah", StartOffset: 0, EndOffset: 4 }, ... ] What I want to do is get a count of how many times each value occurs in the list of tokens. In VB.Net I'd do something…
Basic
  • 26,321
  • 24
  • 115
  • 201
18
votes
2 answers

User defined function to be applied to Window in PySpark?

I am trying to apply a user defined function to Window in PySpark. I have read that UDAF might be the way to to go, but I was not able to find anything concrete. To give an example (taken from here: Xinh's Tech Blog and modified for PySpark): from…
Akavall
  • 82,592
  • 51
  • 207
  • 251
18
votes
3 answers

Is there ANY_VALUE capability for mysql 5.6?

currently im working with mysql 5.7 in development, and 5.6 in production. Each time i run a query with a group by in development i get some error like "Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY " Here is the query. SELECT…
Tim
  • 585
  • 1
  • 5
  • 14
18
votes
2 answers

Grouped string aggregation / LISTAGG for SQL Server

I'm sure this has been asked but I can't quite find the right search terms. Given a schema like this: | CarMakeID | CarMake ------------------------ | 1 | SuperCars | 2 | MehCars | CarMakeID | CarModelID |…
Matt Mitchell
  • 40,943
  • 35
  • 118
  • 185
18
votes
2 answers

Spring Data JPA - Custom Query with multiple aggregate functions in result

I was trying to return an average and count of a set of ratings in one query. I managed it fairly easily in two queries following the example I found browsing. For example: @Query("SELECT AVG(rating) from UserVideoRating where…
formica
  • 934
  • 1
  • 8
  • 16
18
votes
5 answers

Why can't I use SELECT ... FOR UPDATE with aggregate functions?

I have an application where I find a Sum() of a database column for a set of records and later use that sum in a separate query, similar to the following (made up tables, but the idea is the same): SELECT Sum(cost) INTO v_cost_total FROM…
BimmerM3
  • 349
  • 1
  • 3
  • 12
17
votes
1 answer

Postgres window function and group by exception

I'm trying to put together a query that will retrieve the statistics of a user (profit/loss) as a cumulative result, over a period of time. Here's the query I have so far: SELECT p.name, e.date, sum(sp.payout) OVER (ORDER BY e.date) -…
Martin
  • 2,180
  • 4
  • 21
  • 41
1 2 3
…
99 100