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
1620
votes
27 answers

SQL select only rows with max value on a column

I have this table for documents (simplified version here): id rev content 1 1 ... 2 1 ... 1 2 ... 1 3 ... How do I select one row per id and only the greatest rev? With the above data, the result should contain two rows: [1, 3,…
Majid Fouladpour
  • 29,356
  • 21
  • 76
  • 127
464
votes
7 answers

must appear in the GROUP BY clause or be used in an aggregate function

I have a table that looks like this caller 'makerar' cname wmname avg canada zoro 2.0000000000000000 spain luffy 1.00000000000000000000 spain usopp 5.0000000000000000 And I want to select the maximum avg for each cname. SELECT cname,…
RandomGuy
  • 4,949
  • 4
  • 16
  • 15
454
votes
13 answers

SQL: difference between PARTITION BY and GROUP BY

I've been using GROUP BY for all types of aggregate queries over the years. Recently, I've been reverse-engineering some code that uses PARTITION BY to perform aggregations. In reading through all the documentation I can find about PARTITION BY, it…
Mike Mooney
  • 11,729
  • 3
  • 36
  • 42
404
votes
7 answers

Apply multiple functions to multiple groupby columns

The docs show how to apply multiple functions on a groupby object at a time using a dict with the output column names as the keys: In [563]: grouped['D'].agg({'result1' : np.sum, .....: 'result2' : np.mean}) .....: Out[563]:…
beardc
  • 20,283
  • 17
  • 76
  • 94
365
votes
4 answers

Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

I got an error - Column 'Employee.EmpID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. select loc.LocationID, emp.EmpID from Employee as emp full join Location as loc on…
david blaine
  • 5,683
  • 12
  • 46
  • 55
274
votes
38 answers

Function to Calculate Median in SQL Server

According to MSDN, Median is not available as an aggregate function in Transact-SQL. However, I would like to find out whether it is possible to create this functionality (using the Create Aggregate function, user defined function, or some other…
Yaakov Ellis
  • 40,752
  • 27
  • 129
  • 174
224
votes
2 answers

Concatenate multiple result rows of one column into one, group by another column

I'm having a table like this Movie Actor A 1 A 2 A 3 B 4 I want to get the name of a movie and all actors in that movie, and I want the result to be in a format like this: Movie ActorList A 1, 2, 3 How can I do it?
Chin
  • 19,717
  • 37
  • 107
  • 164
219
votes
8 answers

The SQL OVER() clause - when and why is it useful?

USE AdventureWorks2008R2; GO SELECT SalesOrderID, ProductID, OrderQty ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total' ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg' ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID)…
WithFlyingColors
  • 2,650
  • 4
  • 20
  • 25
203
votes
6 answers

Naming returned columns in Pandas aggregate function?

I'm having trouble with Pandas' groupby functionality. I've read the documentation, but I can't see to figure out how to apply aggregate functions to multiple columns and have custom names for those columns. This comes very close, but the data…
David Chouinard
  • 6,466
  • 8
  • 43
  • 61
154
votes
6 answers

How to include "zero" / "0" results in COUNT aggregate?

I've just got myself a little bit stuck with some SQL. I don't think I can phrase the question brilliantly - so let me show you. I have two tables, one called person, one called appointment. I'm trying to return the number of appointments a person…
BrownE
  • 1,679
  • 2
  • 11
  • 13
139
votes
6 answers

Avoid division by zero in PostgreSQL

I'd like to perform division in a SELECT clause. When I join some tables and use aggregate function I often have either null or zero values as the dividers. As for now I only come up with this method of avoiding the division by zero and null values.…
William Wino
  • 3,599
  • 7
  • 38
  • 61
128
votes
8 answers

Optimal way to concatenate/aggregate strings

I'm finding a way to aggregate strings from different rows into a single row. I'm looking to do this in many different places, so having a function to facilitate this would be nice. I've tried solutions using COALESCE and FOR XML, but they just…
matt
  • 2,857
  • 7
  • 33
  • 58
127
votes
24 answers

LISTAGG in Oracle to return distinct values

I am trying to use the LISTAGG function in Oracle. I would like to get only the distinct values for that column. Is there a way in which I can get only the distinct values without creating a function or a procedure? col1 col2 Created_by 1 …
Priyanth
  • 1,379
  • 2
  • 9
  • 5
119
votes
6 answers

MySQL "Group By" and "Order By"

I want to be able to select a bunch of rows from a table of e-mails and group them by the from sender. My query looks like this: SELECT `timestamp`, `fromEmail`, `subject` FROM `incomingEmails` GROUP BY LOWER(`fromEmail`) ORDER BY…
John Kurlak
  • 6,594
  • 7
  • 43
  • 59
108
votes
5 answers

SQL Query to get column values that correspond with MAX value of another column?

Ok, this is my query: SELECT video_category, video_url, video_date, video_title, short_description, MAX(video_id) FROM videos GROUP BY video_category When it pulls the data, I get the correct row for the video_id, but it pulls the…
Devin
  • 2,146
  • 5
  • 24
  • 36
1
2 3
…
99 100