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
101
votes
5 answers

How to sum data.frame column values?

I have a data frame with several columns; some numeric and some character. How to compute the sum of a specific column? I’ve googled for this and I see numerous functions (sum, cumsum, rowsum, rowSums, colSums, aggregate, apply) but I can’t make…
User
  • 62,498
  • 72
  • 186
  • 247
96
votes
4 answers

Spark SQL: apply aggregate functions to a list of columns

Is there a way to apply an aggregate function to all (or a list of) columns of a dataframe, when doing a groupBy? In other words, is there a way to avoid doing this for every column: df.groupBy("col1") .agg(sum("col2").alias("col2"),…
lilloraffa
  • 1,367
  • 3
  • 17
  • 22
93
votes
12 answers

SELECTING with multiple WHERE conditions on same column

Ok, I think I might be overlooking something obvious/simple here... but I need to write a query that returns only records that match multiple criteria on the same column... My table is a very simple linking setup for applying flags to a user ... ID …
RyanNehring
  • 1,033
  • 1
  • 9
  • 9
93
votes
10 answers

GROUP BY without aggregate function

I am trying to understand GROUP BY (new to oracle dbms) without aggregate function. How does it operate? Here is what i have tried. EMP table on which i will run my SQL. SELECT ename , sal FROM emp GROUP BY ename , sal SELECT ename , sal FROM…
XForCE07
  • 1,176
  • 1
  • 10
  • 10
90
votes
4 answers

How to avoid error "aggregate functions are not allowed in WHERE"

This sql code throws an aggregate functions are not allowed in WHERE SELECT o.ID , count(p.CAT) FROM Orders o INNER JOIN Products p ON o.P_ID = p.P_ID WHERE count(p.CAT) > 3 GROUP BY o.ID; How can I avoid this error?
D-Lef
  • 1,219
  • 5
  • 14
  • 20
85
votes
2 answers

Does T-SQL have an aggregate function to concatenate strings?

Possible Duplicates: Implode type function in SQL Server 2000? Concatenate row values T-SQL I have a view which I'm querying that looks like this: BuildingName PollNumber ------------ ---------- Foo Centre 12 Foo Centre …
Brant Bobby
  • 14,956
  • 14
  • 78
  • 115
76
votes
6 answers

Count cumulative total in Postgresql

I am using count and group by to get the number of subscribers registered each day: SELECT created_at, COUNT(email) FROM subscriptions GROUP BY created at; Result: created_at count ----------------- 04-04-2011 100 05-04-2011 …
khairul
  • 1,230
  • 2
  • 12
  • 19
70
votes
4 answers

Count number of times value appears in particular column in MySQL

This has probably been asked before, but I'm unable to make my way through the myriad of search results. Given a non-normalized MySQL table, what is the most optimized query to count the number of times each distinct value of column x was used? e.g.…
Mahmoud Al-Qudsi
  • 28,357
  • 12
  • 85
  • 125
67
votes
2 answers

Django Aggregation - Expression contains mixed types. You must set output_field

I'm trying to achive an Aggregation Query and that's my code: TicketGroup.objects.filter(event=event).aggregate( total_group=Sum(F('total_sold')*F('final_price'))) I have 'total_sold' and 'final_price' in TicketGroup…
Lara
  • 2,170
  • 6
  • 22
  • 43
66
votes
4 answers

Difference between scalar, table-valued, and aggregate functions in SQL server?

What is the difference between scalar-valued, table-valued, and aggregate functions in SQL server? And does calling them from a query need a different method, or do we call them in the same way?
Ehsan Jeihani
  • 1,238
  • 2
  • 14
  • 23
66
votes
5 answers

Postgres - aggregate two columns into one item

I would like to aggregate two columns into one "array" when grouping. Assume a table like so: friends_map: ================================= user_id friend_id confirmed ================================= 1 2 true 1 …
SoluableNonagon
  • 11,541
  • 11
  • 53
  • 98
65
votes
6 answers

Do all columns in a SELECT list have to appear in a GROUP BY clause

My lecturer stated: All column names in SELECT list must appear in GROUP BY clause unless name is used only in an aggregate function. I'm just wanting some confirmation of this as I cannot think of a logical explanation as to why it should be…
user559142
  • 12,279
  • 49
  • 116
  • 179
64
votes
5 answers

GROUP BY + CASE statement

I have a working query that is grouping data by hardware model and a result, but the problem is there are many "results". I have tried to reduce that down to "if result = 0 then keep as 0, else set it to 1". This generally works, but I end up…
ssbsts
  • 844
  • 1
  • 8
  • 13
61
votes
5 answers

Selecting a Record With MAX Value

In SQL Server 2008 I have a table CUSTOMERS that has two columns as: ID, BALANCE How can I write the query that selects the ID of the customer who has maximum balance, "in the most effective way"? Option 1: ORDER BY BALANCE and SELECT TOP(1) -->…
Ahmet Altun
  • 3,910
  • 9
  • 39
  • 64
60
votes
4 answers

use mysql SUM() in a WHERE clause

suppose I have this table id | cash 1 200 2 301 3 101 4 700 and I want to return the first row in which the sum of all the previous cash is greater than a certain value: So for instance, if I want to return the first row in which the…
kamikaze_pilot
  • 14,304
  • 35
  • 111
  • 171
1
2
3
…
99 100