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
46
votes
7 answers

group by first character

I have a problem with a query in Oracle SQL. I have a first_name column in an employees table. I want to group my records according to the first character in first_name. For example, I have 26 records, one with name = 'Alice', one with name = 'Bob',…
sonu
45
votes
7 answers

count without group

I have one table named GUYS(ID, NAME, PHONE), and I need to add a count of how many guys have the same name, and at the same time show all of them. So I can't group them. Example: ID NAME PHONE 1 John 335 2 Harry 444 3 James 367 4 John …
east
  • 451
  • 1
  • 4
  • 3
44
votes
3 answers

Using GROUP BY with FIRST_VALUE and LAST_VALUE

I'm working with some data that is currently stored in 1 minute intervals that looks like this: CREATE TABLE #MinuteData ( [Id] INT , [MinuteBar] DATETIME , [Open] NUMERIC(12, 6) , [High] NUMERIC(12, 6) , [Low]…
Tanner
  • 22,205
  • 9
  • 65
  • 83
44
votes
10 answers

Spark SQL replacement for MySQL's GROUP_CONCAT aggregate function

I have a table of two string type columns (username, friend) and for each username, I want to collect all of its friends on one row, concatenated as strings. For example: ('username1', 'friends1, friends2, friends3') I know MySQL does this with…
Zahra I.S
  • 695
  • 1
  • 10
  • 20
43
votes
2 answers

COUNT CASE and WHEN statement in MySQL

How to use COUNT CASE and WHEN statement in MySQL query, to count when data is NULL and when it is not NULL in one MySQL query?
tanasi
  • 1,804
  • 6
  • 37
  • 53
42
votes
2 answers

How to define and use a User-Defined Aggregate Function in Spark SQL?

I know how to write a UDF in Spark SQL: def belowThreshold(power: Int): Boolean = { return power < -40 } sqlContext.udf.register("belowThreshold", belowThreshold _) Can I do something similar to define an aggregate function? How is…
Rory Byrne
  • 923
  • 1
  • 12
  • 22
41
votes
2 answers

date_trunc 5 minute interval in PostgreSQL

Possible Duplicate: What is the fastest way to truncate timestamps to 5 minutes in Postgres? Postgresql SQL GROUP BY time interval with arbitrary accuracy (down to milli seconds) I want to aggregate data at 5 minute intervals in PostgreSQL. If I…
prateekk
  • 411
  • 1
  • 4
  • 3
39
votes
12 answers

Aggregate SQL Function to grab only the first from each group

I have 2 tables - an Account table and a Users table. Each account can have multiple users. I have a scenario where I want to execute a single query/join against these two tables, but I want all the Account data (Account.*) and only the first set…
Matt
  • 41,216
  • 30
  • 109
  • 147
39
votes
3 answers

Create a pivot table with PostgreSQL

Suppose I have a table in Postgres called listings that looks like this: id neighborhood bedrooms price 1 downtown 0 256888 2 downtown 1 334000 3 riverview 1 505000 etc. How do…
Avishai
  • 4,512
  • 4
  • 41
  • 67
39
votes
4 answers

How to fetch the first and last record of a grouped record in a MySQL query with aggregate functions?

I am trying to fetch the first and the last record of a 'grouped' record. More precisely, I am doing a query like this SELECT MIN(low_price), MAX(high_price), open, close FROM symbols WHERE date BETWEEN(.. ..) GROUP BY YEARWEEK(date) but I'd like…
Jimmy
  • 928
  • 2
  • 9
  • 17
39
votes
3 answers

Two SQL LEFT JOINS produce incorrect result

I have 3 tables: users(id, account_balance) grocery(user_id, date, amount_paid) fishmarket(user_id, date, amount_paid) Both fishmarket and grocery tables may have multiple occurrences for the same user_id with different dates and amounts paid or…
Ryan Bostwick
  • 409
  • 1
  • 4
  • 5
38
votes
2 answers

Aggregate function in MySQL - list (like LISTAGG in Oracle)

I need function, that returns list of strings. I have data in table like this: Id MyString ------------------------ 1 First 2 Second 3 Third 4 Fourth I need function like this (something like this works in oracle): select…
Kamil
  • 13,363
  • 24
  • 88
  • 183
36
votes
7 answers

Example of the Scala aggregate function

I have been looking and I cannot find an example or discussion of the aggregate function in Scala that I can understand. It seems pretty powerful. Can this function be used to reduce the values of tuples to make a multimap-type collection? For…
christangrant
  • 9,276
  • 4
  • 26
  • 27
35
votes
4 answers

Why are aggregate functions not allowed in where clause

I am looking for clarification on this. I am writing two queries below: We have a table of employee name with columns ID , name , salary 1. Select name from employee where sum(salary) > 1000 ; 2. Select name from employee where…
Nishant_Singh
  • 748
  • 1
  • 6
  • 16
34
votes
4 answers

Count rows with a specific condition in aggregate query

I have this query to get the number of PlayerSessions with reconnect = TRUE, grouped by Player.country: SELECT country, COUNT(*) AS with_reconnect FROM PlayerSession S LEFT JOIN Player P ON (P.id = S.player_id) WHERE reconnect = TRUE GROUP…
Bart van Heukelom
  • 43,244
  • 59
  • 186
  • 301
1 2 3
…
99 100