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
3 answers

SQL using a WHERE statement with an aggregate function

The following code runs exactly the way I want it to. Now I need to achieve the same task but only showing results with a COUNT greater than 1. The COUNT is meant to count the amount of rows in the allocation table of each distinct book id…
Ruzzy
  • 67
  • 6
2
votes
2 answers

Custom PostgreSQL aggregate for circular average

I'm trying to implement a custom aggregate function in Postgres which will average directions in degrees - i.e. I want to able to do: SELECT circavg(direction) FROM sometable; This can be done using the formula: xbar = atan2(sum(sin(xi),…
samwise
  • 129
  • 7
2
votes
3 answers

How to sum data of two different columns of two different tables?

i have two tables i my database in which i have two columns in each table one is for userid & one is for showing some numeric data. I just want to add the value of the numeric column but the issue is that it's not compulsory that both the table have…
Peeyush
  • 4,728
  • 16
  • 64
  • 92
2
votes
0 answers

Aggregate values for Doctrine_RawSql queries for use as doctrine objects

I'm building an app on Symfony 1.4/Doctrine 1.2 ORM and am having trouble with implementing a MYSQL query to be used as a doctrine object in my view Here is the working MYSQL query that I need to hydrate into a doctrine object: SELECT u.id,…
frankp221
  • 175
  • 3
  • 11
1
vote
1 answer

MYSQL: Problems using COUNT and SUM functions in multi row select

I am attempting to create a procedure that selects ads from a database in rotation. And ultimately returns the selected ads row and increments the views value. The part I am currently working on should Pull results from the 'ads' table WHERE the…
christian
  • 2,279
  • 4
  • 31
  • 42
1
vote
2 answers

Postgres array comparison confusion

When I run select array[19,21,500] <= array[23,5,0]; I get true. but when I run select array[24,21,500] <= array[23,5,0]; I get false. This suggests that the comparison is only on the first element. I am wondering if there is an operator or…
user1286546
  • 23
  • 4
1
vote
3 answers

Create two arrays for two fields, keeping sort order of arrays in sync (without subquery)

There is no rhyme or reason for this question other than I was curious about how one would go about doing this. Platform: while I was hoping for a SQL-Standard solution, my main concentration is with PostgreSQL 8.4+. (I know 9.0+ has some array…
vol7ron
  • 40,809
  • 21
  • 119
  • 172
1
vote
1 answer

MYSQL join with sort and group by choosing the element to display

I have 2 tables, evt and content. content is link to evt throw the column content_evt_fk (to make it simpler, you can replace evt by article and content by comment for a blog database). What I'm trying to do is to have in one query, the evt id, the…
yumyum
  • 122
  • 8
1
vote
2 answers

Query to get peak hours from session table

I have a table containing user sessions, i.e. a period a user is logged in on my application. Each session has a start and end timestamp. Can I, with one query, determine for every hour in the day how many users were online at that time (i.e. how…
Bart van Heukelom
  • 43,244
  • 59
  • 186
  • 301
1
vote
5 answers

Finding the 2nd most expensive total products in MySQL

I'm working on simple queries to learn MySQL, in my example database, I keep track of Stores which sells electronic devices, I have a table Sells(Store, Item, Price). And example data is, 'Best Buy', 'Galaxy S', 1000 'Buy More', 'Macbook Air', 2000…
CanCeylan
  • 2,890
  • 8
  • 41
  • 51
1
vote
1 answer

Misuse of aggregate in SQLite

This error occurs in this SQLite query: select f.rid, c.title, sum(some_expression) as ratio from doesntmatter where c.active = 1 or (ratio = 1.0 and c.active = 0 and c.deactivated in (1, 2, 3, 4, 5) group by f.rid This question solves it…
xralf
  • 3,312
  • 45
  • 129
  • 200
1
vote
3 answers

T-SQL - aggregate in all rows using aggregate from correlated sub-query

store item datekey onhand salesunits -------------------------------------------- 001 A 50 65 2 001 A 51 8 4 001 A 52 0 8 -------------------------------------------- What…
Gus Cavalcanti
  • 10,527
  • 23
  • 71
  • 104
1
vote
1 answer

Running total of "matches" using a window function in SQL

I want to create a window function that will count how many times the value of the field in the current row appears in the part of the ordered partition coming before the current row. To make this more concrete, suppose we have a table like so: |…
John Horton
  • 4,122
  • 6
  • 31
  • 45
1
vote
2 answers

SELECT *,LAST(date_time)

I'm running a while loop to get the most recent message of each conversation, however I am getting the error [18-Feb-2012 21:14:59] PHP Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /path/messages.php on…
Sam
  • 6,616
  • 8
  • 35
  • 64
1
vote
2 answers

Aggregate several values from a field, depending on a code from another field

I need to calculate several values from a field in a table depending on a code in another field of the same table. The table is in a PostgreSQL 8.3 database. Table: cod_1 | cod_2 | date | cod_sum| import 1 | 2 | 01/01/2012 | a |…
bl4ckb1rd
  • 143
  • 2
  • 8
1 2 3
…
99
100