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

GROUP BY and COUNT in PostgreSQL

The query: SELECT COUNT(*) as count_all, posts.id as post_id FROM posts INNER JOIN votes ON votes.post_id = posts.id GROUP BY posts.id; Returns n records in Postgresql: count_all | post_id -----------+--------- 1 | 6 3 …
skinkelynet
  • 857
  • 2
  • 9
  • 14
58
votes
3 answers

MySQL: GROUP_CONCAT with LEFT JOIN

I'm experiencing a problem with MySQL's "GROUP_CONCAT" function. I will illustrate my problem using a simple help desk database: CREATE TABLE Tickets ( id INTEGER NOT NULL PRIMARY KEY, requester_name VARCHAR(255) NOT NULL, description TEXT NOT…
Nick
  • 591
  • 1
  • 4
  • 5
57
votes
4 answers

mysql count group by having

I have this table: Movies (ID, Genre) A movie can have multiple genres, so an ID is not specific to a genre, it is a many to many relationship. I want a query to find the total number of movies which have at exactly 4 genres. The current query I…
Michael Liao
  • 2,623
  • 3
  • 17
  • 10
54
votes
7 answers

How to use Oracle's LISTAGG function with a unique filter?

I have a table like this: group_id name -------- ---- 1 David 1 John 1 Alan 1 David 2 Julie 2 Charles And I want the following result: group_id names -------- ----- 1 'Alan, David,…
daveslab
  • 10,000
  • 21
  • 60
  • 86
54
votes
8 answers

How to select data where a field has a min value in MySQL?

I want to select data from a table in MySQL where a specific field has the minimum value, I've tried this: SELECT * FROM pieces WHERE MIN(price) Please any help?
Sami El Hilali
  • 981
  • 3
  • 12
  • 21
52
votes
1 answer

How to group mysql rows with same column value into one row?

I have two tables, keywords and data. Table keywords have 2 columns (id, keyword), table data have 3 columns (id[foreign key of keywords.id], name, value). I am using this query: SELECT k.id, d.value, d.name FROM keywords AS k INNER JOIN data as d…
jarkam
  • 1,538
  • 5
  • 15
  • 20
52
votes
4 answers

DISTINCT ON in an aggregate function in postgres

For my problem, we have a schema whereby one photo has many tags and also many comments. So if I have a query where I want all the comments and tags, it will multiply the rows together. So if one photo has 2 tags and 13 comments, I get 26 rows for…
Migwell
  • 18,631
  • 21
  • 91
  • 160
51
votes
8 answers

How can I use SUM for bit columns?

How can use the function SUM() for bit columns in T-SQL? When I try do it as below: SELECT SUM(bitColumn) FROM MyTable; I get the error: Operand data type bit is invalid for sum operator.
Bruno Pessanha
  • 2,874
  • 4
  • 24
  • 35
51
votes
2 answers

in postgres select, return a column subquery as an array?

(have done this before, but memory fades, as does goggle) wish to get select from users with the tag.tag_ids for each user returned as an array. select usr_id, name, (select t.tag_id from tags t where t.usr_id = u.usr_id) as…
cc young
  • 18,939
  • 31
  • 90
  • 148
51
votes
1 answer

No non-missing arguments warning when using min or max in reshape2

I get the following warning when I use min or max in the dcast function from the reshape2 package. What is it telling me? I can't find anything that explains the warning message and I'm a bit confused about why I get it when I use max but not when I…
Tumbledown
  • 1,887
  • 5
  • 21
  • 33
51
votes
6 answers

How to do a count on a union query

I have the following query: select distinct profile_id from userprofile_... union select distinct profile_id from productions_... How would I get the count of the total number of results?
David542
  • 104,438
  • 178
  • 489
  • 842
49
votes
6 answers

How to SUM and SUBTRACT using SQL?

I am using MySQL and I have two tables: master_table ORDERNO ITEM QTY stock_bal ITEM BAL_QTY Master table has duplicate ORDERNO and ITEM values. I have get total QTY using SQL 'GROUP BY' clause. I need to deduct/subtract BAL_QTY from SUM of…
Tharindu ucsc
  • 631
  • 1
  • 8
  • 17
48
votes
9 answers

LINQ aggregate and group by periods of time

I'm trying to understand how LINQ can be used to group data by intervals of time; and then ideally aggregate each group. Finding numerous examples with explicit date ranges, I'm trying to group by periods such as 5-minutes, 1-hour, 1-day. For…
Jason Sturges
  • 15,855
  • 14
  • 59
  • 80
47
votes
1 answer

PostgreSQL: running count of rows for a query 'by minute'

I need to query for each minute the total count of rows up to that minute. The best I could achieve so far doesn't do the trick. It returns count per minute, not the total count up to each minute: SELECT COUNT(id) AS count , EXTRACT(hour from…
GabiMe
  • 18,105
  • 28
  • 76
  • 113
47
votes
3 answers

Return multiple columns of the same row as JSON array of objects

I have the following table MyTable: id │ value_two │ value_three │ value_four ────┼───────────┼─────────────┼──────────── 1 │ a │ A │ AA 2 │ a │ A2 │ AA2 3 │ b │ A3 │ AA3 4 │ a │…
ehmicky
  • 1,915
  • 4
  • 20
  • 29
1 2
3
…
99 100