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

How to create an empty JSON object in postgresql?

Datamodel A person is represented in the database as a meta table row with a name and with multiple attributes which are stored in the data table as key-value pair (key and value are in separate columns). Simplified data-model Now there is a query…
cansik
  • 1,924
  • 4
  • 19
  • 39
24
votes
2 answers

Unexpected results when using FIRST_VALUE() in SQL Server 2012

When I use FIRST_VALUE on a data set that I construct by hand I get one result, and when I use it on a data set that results from a left join, I get a different result - even though the data sets appear to me to contain the exact same data values.…
Salah at Soap Hope
  • 287
  • 2
  • 12
24
votes
2 answers

Why is PostgreSQL array access so much faster in C than in PL/pgSQL?

I have a table schema which includes an int array column, and a custom aggregate function which sums the array contents. In other words, given the following: CREATE TABLE foo (stuff INT[]); INSERT INTO foo VALUES ({ 1, 2, 3 }); INSERT INTO foo…
Matt Solnit
  • 32,152
  • 8
  • 53
  • 57
24
votes
2 answers

How to sort by annotated Count() in a related model in Django

I'm building a food logging database in Django and I've got a query related problem. I've set up my models to include (among other things) a Food model connected to the User model through an M2M-field "consumer" via the Consumption model. The Food…
Jens Alm
  • 3,027
  • 4
  • 22
  • 24
23
votes
3 answers

SQL Query to get aggregated result in comma separators along with group by column in SQL Server

I need to write a sql query on the table such that the result would have the group by column along with the aggregated column with comma separators. My table would be in the below format |`````````|````````| | ID | Value | …
suryakiran
  • 1,976
  • 25
  • 41
23
votes
6 answers

Why can't you mix Aggregate values and Non-Aggregate values in a single SELECT?

I know that if you have one aggregate function in a SELECT statement, then all the other values in the statement must be either aggregate functions, or listed in a GROUP BY clause. I don't understand why that's the case. If I do: SELECT Name,…
TarkaDaal
  • 18,798
  • 7
  • 34
  • 51
23
votes
2 answers

postgres array_agg ERROR: cannot accumulate arrays of different dimensionality

I have a parcels table in postgresql in which the zoning and zoning_description columns are array_agg cast over to text. the new.universities table has 9 rows and I need to return 9 rows in the output. The purpose of this query is to find all the…
ziggy
  • 1,488
  • 5
  • 23
  • 51
23
votes
6 answers

MySQL: How to SUM() a TIMEDIFF() on a group?

So I've got a set of results that looks something like this: SELECT User_ID, StartTime, EndTime, TIMEDIFF(EndTime, StartTime) AS TimeDiff FROM MyTable ------------------------------------------------------------------ | User_ID | StartTime …
Andrew
  • 227,796
  • 193
  • 515
  • 708
22
votes
4 answers

How to load extensions into SQLite?

I need a standard deviation function in SQLite. I have found one here: http://www.sqlite.org/contrib?orderby=date but its part of an extension file to SQLite. I've never installed one of these before and I don't know how to. I found this existing…
Chucky
  • 1,701
  • 7
  • 28
  • 62
22
votes
2 answers

Explain R tapply description

I understand what tapply() does in R. However, I cannot parse this description of it from the documentaion: Apply a Function Over a "Ragged" Array Description: Apply a function to each cell of a ragged array, that is to each …
frankc
  • 11,290
  • 4
  • 32
  • 49
22
votes
6 answers

How do I calculate a running SUM?

How do I get a column that is the sum of the preceding values of another column?
Hugo
  • 1,558
  • 12
  • 35
  • 68
22
votes
3 answers

postgresql Aggregate arrays into a single array with union of all elements

I'm looking for a sql pattern for an aggregate function to aggregate arrays. If I have 2 rows: |id | array | |----+---------------| |1 | [1,2,3,4] | |1 | [5,6] | |1 | [7,8] | |--------------------| And I want to…
skmathur
  • 1,587
  • 5
  • 14
  • 21
22
votes
7 answers

How to count NULL values in MySQL?

I want to know how can i find all the values that are NULL in the MySQL database for example I'm trying to display all the users who don't have an average yet. Here is the MySQL code. SELECT COUNT(average) as num FROM users WHERE user_id =…
abbr
  • 223
  • 1
  • 2
  • 4
22
votes
2 answers

Postgres CASE in ORDER BY using an alias

I have the following query which works great in Postgres 9.1: SELECT users.id, GREATEST( COALESCE(MAX(messages.created_at), '2012-07-25 16:05:41.870117'), COALESCE(MAX(phone_calls.created_at), '2012-07-25 16:05:41.870117') ) AS…
dwilkie
  • 583
  • 1
  • 4
  • 9
21
votes
2 answers

How to use a SQL window function to calculate a percentage of an aggregate

I need to calculate percentages of various dimensions in a table. I'd like to simplify things by using window functions to calculate the denominator, however I am having an issue because the numerator has to be an aggregate as well. As a simple…
EvilPuppetMaster
  • 8,072
  • 10
  • 34
  • 31
1 2 3
…
99 100