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
2
votes
1 answer

Can get an average of values in a json array using postgres?

One of the great things about postgres is that it allows indexing into a json object. I have a column of data formatted a little bit like this: {"Items": [ {"RetailPrice":6.1,"EffectivePrice":0,"Multiplier":1,"ItemId":"53636"}, …
johncorser
  • 9,262
  • 17
  • 57
  • 102
2
votes
3 answers

WHERE in Aggregate function

I have a quick question. I know that we cannot use WHERE clause in an aggregate function in MySQL. The table structure is as follows: +----+------+----------+--------+ | ID | Name | Location | Active | +----+------+----------+--------+ | 1 | Aaaa…
Praveen Kumar Purushothaman
  • 164,888
  • 24
  • 203
  • 252
2
votes
2 answers

SQL Select only rows with Max Value on a Column FILTERED by Column

This is a followup question to the excellent answer: SQL Select only rows with Max Value on a Column SQLFiddle http://sqlfiddle.com/#!2/3077f/2 Table "yourtable": | id | val | ignore | content | ------------------------------- | 1 | 10 | 0 | …
Yaniv
  • 113
  • 10
2
votes
4 answers

SELECT a different aggregate value if null?

So let's say I have the following data samples: Sample 1: id|name ------- 0 |0 0 |1 0 |2 0 |3 ------- Sample 2: id|name ------- 0 |0 1 |1 3 |3 4 |4 ------- Sample 3: id|name ------- 0 |0 1 |1 2 |2 3 |3 ------- What I want is to be able to return…
mmtauqir
  • 8,499
  • 9
  • 34
  • 42
2
votes
4 answers

SQL return a Distinct column and the first date of the distinct column

I have a database that contains multiple url and a date (created_at) associate with each of these url. I would like to have something like: Select DISTINCT url, "the first date of this url" from database where blabala My problem is when a add the…
CodeName
  • 385
  • 5
  • 12
2
votes
2 answers

Select a value inside a select statement

I have two tables: BOOKS and USERS_BOOKS: BOOKS: | ID | BOOKNAME | |----|----------| | 1 | Book1 | | 2 | Book2 | | 3 | Book3 | | 4 | Book4 | | 5 | Book5 | USERS_BOOKS: | ID | USERID | BOOKID | STATUS…
erdomester
  • 11,789
  • 32
  • 132
  • 234
2
votes
1 answer

Can I force this Oracle windowing query to return a single row of values?

This query is meant to replace another that uses nested subqueries and performs accordingly. As it is now, it does return the correct results for each column, but will return a row that has the value for that column, nulls for the rest, then another…
John O
  • 4,863
  • 8
  • 45
  • 78
2
votes
3 answers

Grouping based on sequence of rows

I have a table of orders with a column denoting whether it's a buy or a sell, with the rows typically ordered by timestamp. What I'd like to do is operate on groups of consecutive buys, plus their sell. e.g. B B S B S B B S -> (B B S) (B S) (B B…
Alex
  • 21
  • 2
2
votes
2 answers

Aggregate function in group by multiple columns

I have a table with the following columns: ClientID, OrderID, ProductID, Quantity with each ProductID appearing only once per OrderID, and each OrderID corresponding to only one ClientID. Sample Data: ClientID OrderID ProductID Quantity 1 …
ovidiufelixb
  • 45
  • 3
  • 11
2
votes
1 answer

pandas dataframe groupby summation

input: Date letters numbers mixed new 0 1/2/2014 a 6 z1 1/2/2014 a 1 1/2/2014 a 3 z1 1/2/2014 a 2 1/3/2014 c 1 x3 1/3/2014 c I want to groupby new and sum numbers so that the output…
jason
  • 3,811
  • 18
  • 92
  • 147
2
votes
1 answer

Should I avoid using iterators starting Java 8? Do they still have appropriate uses?

Now that we have aggregate operations, is there still any use for iterators? What cases?
jantristanmilan
  • 4,188
  • 14
  • 53
  • 69
2
votes
2 answers

Rails aggregate query counting rows that satisfy certain conditions

Let's say that have a table called bets. I want to run an aggregate sql query that counts rows satisfying certain conditions. For example, I want to return a count of all bets won, all bets lost, etc. I also want these counts to be grouped by…
keruilin
  • 16,782
  • 34
  • 108
  • 175
2
votes
1 answer

How to get the sum under different conditions in one query?

How to do the following in one query : If i have two queries like that SELECT SUM(value +sal) AS sump FROM loans WHERE active_flag = 1 SELECT SUM(value +sal) AS sumn FROM loans WHERE active_flag = 2 I want to get sump and sumn in the same…
Anyname Donotcare
  • 11,113
  • 66
  • 219
  • 392
2
votes
1 answer

To generate a 16 byte char type unique ID with PL/SQL

I know, that SYS_GUID() returns a 16 byte RAW datatype. But when it transfer to chars, it will be over 32 byte char type. That's not compatible to the existing data with a max length of char(16). So is it possible to use the Oracle aggregate…
Martin T Y Ho
  • 79
  • 7
2
votes
1 answer

Is this LINQ query with averaging and grouping by hour written efficiently?

This is my first real-world LINQ-to-SQL query. I was wondering if I am making any large, obvious mistakes. I have a medium-large sized (2M+ records, adding 13k a day) table with data, dataTypeID, machineID, and dateStamp. I'd like to get the…
PHiZiX
  • 23
  • 3
1 2 3
…
99 100