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

how to group by and return sum row in Postgres

I am stuck here. I have row in Postgres like this: id amount a 5000 a 1500 a 500 b 2000 b 1000 c 4000 How is the sql syntax to get result like this? id amount a 7000 b 3000 c 4000
kuslahne
  • 720
  • 4
  • 10
  • 21
34
votes
3 answers

Prolog count the number of times a predicate is true

I want to count the number of times a custom predicate is true. For example, I have the following code: is_man(john). is_man(alex). ?:-is_man(X). X will return john, then if I press semicolon it will also return alex, then false. I want to build…
Victor Blaga
  • 1,822
  • 4
  • 19
  • 28
34
votes
5 answers

MySQL: Select N rows, but with only unique values in one column

Given this data set: ID Name City Birthyear 1 Egon Spengler New York 1957 2 Mac Taylor New York 1955 3 Sarah Connor Los Angeles 1959 4 Jean-Luc Picard La Barre 2305 5 Ellen Ripley …
BlaM
  • 28,465
  • 32
  • 91
  • 105
31
votes
7 answers

MySQL Update Column +1?

I was wondering what would be the easiest way to update a column by +1? I will be updating a post count of a category based on when users submits a new post. Thanks.
Cory
  • 732
  • 3
  • 7
  • 22
31
votes
4 answers

SqlAlchemy: count of distinct over multiple columns

I can't do: >>> session.query( func.count(distinct(Hit.ip_address, Hit.user_agent)).first() TypeError: distinct() takes exactly 1 argument (2 given) I can do: session.query( func.count(distinct(func.concat(Hit.ip_address,…
EoghanM
  • 25,161
  • 23
  • 90
  • 123
30
votes
2 answers

Applying a custom groupby aggregate function to output a binary outcome in pandas python

I have a dataset of trader transactions where the variable of interest is Buy/Sell which is binary and takes on the value of 1 f the transaction was a buy and 0 if it is a sell. An example looks as follows: Trader Buy/Sell A 1 A …
finstats
  • 1,349
  • 4
  • 19
  • 31
29
votes
7 answers

Is it possible to use Aggregate function in a Select statment without using Group By clause?

So far I have written Aggregate function followed by Group By clause to find the values based on SUM, AVG and other Aggregate functions. I have a bit confusion in the Group By clause. When we use Aggregate functions what are the columns I need to…
thevan
  • 10,052
  • 53
  • 137
  • 202
29
votes
6 answers

Custom aggregate function (concat) in SQL Server

Question: I want to write a custom aggregate function that concatenates string on group by. So that I can do a SELECT SUM(FIELD1) as f1, MYCONCAT(FIELD2) as f2 FROM TABLE_XY GROUP BY FIELD1, FIELD2 All I find is SQL CRL aggregate functions, but I…
Stefan Steiger
  • 78,642
  • 66
  • 377
  • 442
28
votes
3 answers

Create nested json from sql query postgres 9.4

I need to get as a result from query fully structured JSON. I can see in postgres that there are some built in functions that may be useful. As an example I created a structure as follows: -- Table: person -- DROP TABLE person; CREATE TABLE…
Snorlax
  • 787
  • 2
  • 9
  • 22
28
votes
1 answer

Best way to count rows by arbitrary time intervals

My app has a Events table with time-stamped events. I need to report the count of events during each of the most recent N time intervals. For different reports, the interval could be "each week" or "each day" or "each hour" or "each 15-minute…
jpw
  • 18,697
  • 25
  • 111
  • 187
27
votes
6 answers

Group DateTime into 5,15,30 and 60 minute intervals

I am trying to group some records into 5-, 15-, 30- and 60-minute intervals: SELECT AVG(value) as "AvgValue", sample_date/(5*60) as "TimeFive" FROM DATA WHERE id = 123 AND sample_date >= 3/21/2012 i want to run several queries, each would group my…
jrubengb
  • 363
  • 1
  • 7
  • 14
27
votes
3 answers

How to define a custom aggregation function to sum a column of Vectors?

I have a DataFrame of two columns, ID of type Int and Vec of type Vector (org.apache.spark.mllib.linalg.Vector). The DataFrame looks like follow: ID,Vec 1,[0,0,5] 1,[4,0,1] 1,[1,2,1] 2,[7,5,0] 2,[3,3,4] 3,[0,8,1] 3,[0,0,1] 3,[7,7,7] .... I would…
Rami
  • 8,044
  • 18
  • 66
  • 108
25
votes
4 answers

Oracle SQL - Sum and group data by week

I have records related to dates: DATE AMOUNT 16.03.2013 3 16.03.2013 4 16.03.2013 1 16.03.2013 3 17.03.2013 4 17.03.2014 3 I know how to sum them up for each day, but how could I sum them up by week?`
royskatt
  • 1,190
  • 2
  • 15
  • 35
24
votes
4 answers

Extra Fields with SQL MIN() & GROUP BY

When using the SQL MIN() function, along with GROUP BY, will any additional columns (not the MIN column, or one of the GROUP BY columns) match the data in the matching MIN row? For example, given a table with department names, employee names, and…
Tim Lytle
  • 17,549
  • 10
  • 60
  • 91
24
votes
5 answers

Aggregate Relational Algebra (Maximum)

I am currently working on a homework assignment that requires a selection to occur that pulls out an element containing a specific attribute of maximum value compared to all other records. I've read a number of sources online that reference an…
XBigTK13X
  • 2,655
  • 8
  • 30
  • 39
1 2 3
…
99 100