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

How can I compare two fields of a model in a query?

I'm writing a management command which will filter a product's original price with suggested prices. I have a product model which looks like : class Suggestion(models.Model): .... price = models.IntegerField() class Product(models.Model): …
iva123
  • 3,395
  • 10
  • 47
  • 68
17
votes
6 answers

mysql count the sum of all rows

I have a mysql table that has a number of rows, and in each row a field called "value", the field value will differ from row to row. What I want, is to select all the rows and count the sum of all the "value" fields. any idea?
med
  • 449
  • 2
  • 5
  • 16
17
votes
1 answer

How to update fields that is the aggregate result of another table in MySQL?

UPDATE a JOIN b ON a.app_id=b.app_id GROUP BY a.app_id SET remark_avg=AVG(b.score),remark_count=COUNT(b.id); The above is basically what I want to do,but it's not a valid MySQL statement,how to write it correctly?
gdb
  • 7,189
  • 12
  • 38
  • 36
17
votes
2 answers

SQL: tuple comparison

In my current application, I need to be able to do this type of query: SELECT MIN((colA, colB, colC)) FROM mytable WHERE (colA, colB, colC) BETWEEN (200, 'B', 'C') AND (1000, 'E', 'F') and get the answer of (333, 'B', 'B'), given this…
bukzor
  • 37,539
  • 11
  • 77
  • 111
17
votes
5 answers

MAX() and MAX() OVER PARTITION BY produces error 3504 in Teradata Query

I am trying to produce a results table with the last completed course date for each course code, as well as the last completed course code overall for each employee. Below is my query: SELECT employee_number, MAX(course_completion_date) …
dneaster3
  • 309
  • 1
  • 3
  • 10
17
votes
1 answer

Why is count(distinct) slower than group by in Hive?

On Hive, I believe count(distinct) will be more likely than group-by to result in an unbalanced workload to reducers and end up with one sad reducer grinding away. Example query below. Why? Example query: select count(distinct user) from…
dfrankow
  • 20,191
  • 41
  • 152
  • 214
16
votes
2 answers

Best performance in sampling repeated value from a grouped column

This question is about the functionality of first_value(), using another function or workaround. It is also about "little gain in performance" in big tables. To use eg. max() in the explained context below, demands spurious comparisons. Even if…
Peter Krauss
  • 13,174
  • 24
  • 167
  • 304
16
votes
2 answers

Multiple array_agg() calls in a single query

I'm trying to accomplish something with my query but it's not really working. My application used to have a mongo db so the application is used to get arrays in a field, now we had to change to Postgres and I don't want to change my applications…
user1391281
  • 215
  • 1
  • 3
  • 10
16
votes
5 answers

PostgreSQL - string_agg with limited number of elements

Is it possible to limit the number of elements in the following string_agg function? string_agg(distinct(tag),', ')
nickbusted
  • 1,029
  • 4
  • 18
  • 30
16
votes
4 answers

Ordering distinct column values by (first value of) other column in aggregate function

I'm trying to order the output order of some distinct aggregated text based on the value of another column with something like: string_agg(DISTINCT sometext, ' ' ORDER BY numval) However, that results in the error: ERROR: in an aggregate with…
Dologan
  • 4,554
  • 2
  • 31
  • 33
16
votes
2 answers

MySql Select, Count(*) and SubQueries in Users<>Comments relations

I have a task to count the quantity of users having count of comments > X. My SQL-query looks like this: SELECT users.id, users.display_name, (SELECT COUNT(*) FROM cms_comments WHERE cms_comments.author_id =…
WesternTune
  • 199
  • 1
  • 1
  • 8
15
votes
4 answers

Nesting Aggregate Functions - SQL

I want to make a SQL query which finds the catagory of awards for movies which has the highest average rating, so for a group of movies which have won a particular award, if they have a higher average rating than any other awards group of movies…
steve
  • 209
  • 1
  • 3
  • 10
15
votes
4 answers

SQL comma-separated row with Group By clause

I have the following query: SELECT Account, Unit, SUM(state_fee), Code FROM tblMta WHERE MTA.Id = '123' GROUP BY Account,Unit This of course throws an exception because the Code is not in the group by clause. Each state_fee has a code. How…
mameesh
  • 3,651
  • 9
  • 37
  • 47
15
votes
2 answers

oracle sql query question(grouping by 2 columns)

I have a table called testgroup in my database, which is like following: I J ---------------------- ---------------------- 1 a 1 a …
csuo
  • 820
  • 3
  • 16
  • 31
15
votes
5 answers

LEFT JOIN query with JSON object array aggregate

I have 2 tables: Table person with columns: person_id, person_name Table pet with columns: pet_id, owner_id, pet_name person data: 1, 'John' 2, 'Jill' 3, 'Mary' pet data: 1, 1, 'Fluffy' 2, 1, 'Buster' 3, 2, 'Doggy' How to write a SELECT query from…
Tom Berghuis
  • 491
  • 3
  • 10
  • 26
1 2 3
…
99 100