Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [aggregate]

Combining multiple data points into a single data point. This usually is used in reference to SQL and usually to an aggregate function of some sort.

Combining multiple data points into a single data point. This usually is used in reference to SQL and usually to an aggregate function of some sort.

Aggregate functions return a single value per row based on a group of rows. Common examples include COUNT and SUM.

634 questions
111
votes
5 answers

Storing vs calculating aggregate values

Are there any guidelines or rules of thumb to determine when to store aggregate values and when to calculate them on the fly? For example, suppose I have widgets which users can rate (see schema below). Each time I display a widget I could…
BenV
  • 4,923
  • 7
  • 40
  • 38
81
votes
7 answers

Writing a simple bank schema: How should I keep my balances in sync with their transaction history?

I am writing the schema for a simple bank database. Here are the basic specifications: The database will store transactions against a user and currency. Every user has one balance per currency, so each balance is simply the sum of all…
Nick Chammas
  • 14,810
  • 17
  • 76
  • 124
62
votes
4 answers

What is the difference between select count(*) and select count(any_non_null_column)?

I seem to remember that (on Oracle) there is a difference between uttering select count(*) from any_table and select count(any_non_null_column) from any_table. What are the differences between these two statements, if any?
Martin
  • 2,420
  • 4
  • 26
  • 35
55
votes
5 answers

How can I use a default value in a Select query in PostgreSQL?

I would like to use a default value for a column that should be used if no rows is returned. Is that possible in PostgreSQL? How can I do it? Or is there any other way I can solve this? E.g. something like this: SELECT MAX(post_id) AS max_id DEFAULT…
Jonas
  • 33,945
  • 27
  • 62
  • 64
54
votes
2 answers

Convert right side of join of many to many into array

When using join on many to many relationship the result is split on multiple rows. What I'd like to do is convert the right side of a join into an array so the result is one row. Example with 3 tables: CREATE TABLE items ( id serial primary…
Ced
  • 754
  • 2
  • 7
  • 10
49
votes
15 answers

Eliminate duplicates in ListAgg (Oracle)

Prior to Oracle 11.2 I was using a custom aggregate function to concatenate a column into a row. 11.2 Added the LISTAGG function, so I am trying to use that instead. My problem is that I need to eliminate duplicates in the results and don't seem…
Leigh Riffel
  • 23,884
  • 17
  • 80
  • 155
39
votes
5 answers

In PostgreSQL, is there a type-safe first() aggregate function?

I'm looking for a first() aggregate function. Here I found something that almost works: CREATE OR REPLACE FUNCTION public.first_agg (anyelement, anyelement) RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$ SELECT $1; $$; -- And then wrap…
Alexandre Neto
  • 577
  • 2
  • 5
  • 11
36
votes
4 answers

Use of HAVING without GROUP BY in SQL queries

In order to use HAVING in SQL queries , must there be a GROUP BY to aggregate the column names? Are there any special cases where it is possible to use HAVING without a GROUP BY in SQL queries? Must they co-exist at the same time?
Computernerd
  • 483
  • 1
  • 5
  • 8
34
votes
1 answer

Postgres error [column must appear in the GROUP BY clause or be used in an aggregate function] when sub query is used

I have two tables employee and phones. An employee can have 0 to n phone numbers. I want to list the employee names with their phone numbers. I am using the below query which runs fine. SELECT empname,array_agg(phonenumber) AS phonenumbers FROM…
Programmer
  • 443
  • 2
  • 5
  • 6
33
votes
6 answers

Combining separate ranges into largest possible contiguous ranges

I'm trying to combine multiple date ranges (my load is about max 500, most cases 10) that may or may not overlap into the largest possible contiguous date ranges. For example: Data: CREATE TABLE test ( id SERIAL PRIMARY KEY NOT NULL, range…
Villiers Strauss
  • 627
  • 2
  • 6
  • 9
30
votes
3 answers

Why does ANSI SQL define SUM(no rows) as NULL?

The ANSI SQL standard defines (chapter 6.5, set function specification) the following behaviour for aggregate functions on empty result sets: COUNT(...) = 0 AVG(...) = NULL MIN(...) = NULL MAX(...) = NULL SUM(...) = NULL Returning NULL for AVG, MIN…
Heinzi
  • 3,210
  • 2
  • 32
  • 43
29
votes
7 answers

What is the most efficient way to get the minimum of multiple columns on SQL Server 2005?

I'm in a situation where I want to get the minimum value from of 6 columns. I've found three ways so far to accomplish this, but I have concerns with the performance of these methods and would like to know which would be better for performance. The…
Rachel
  • 8,547
  • 20
  • 51
  • 74
29
votes
2 answers

SQL Server returns "Arithmetic overflow error converting expression to data type int."

When I run this command with SUM() SELECT COUNT(*) AS [Records], SUM(t.Amount) AS [Total] FROM dbo.t1 AS t WHERE t.Id > 0 AND t.Id < 101; I'm getting, Arithmetic overflow error converting expression to data type int. Any idea on what is…
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
26
votes
1 answer

Rolling sum / count / average over date interval

In a database of transactions spanning 1,000s of entities over 18 months, I would like to run a query to group every possible 30-day period by entity_id with a SUM of their transaction amounts and COUNT of their transactions in that 30-day period,…
tufelkinder
  • 363
  • 1
  • 3
  • 7
22
votes
3 answers

What is the correct result for this query?

I came across this puzzle in the comments here CREATE TABLE r (b INT); SELECT 1 FROM r HAVING 1=1; SQL Server and PostgreSQL return 1 row. MySQL and Oracle return zero rows. Which is correct? Or are both equally valid?
Martin Smith
  • 87,941
  • 15
  • 255
  • 354
1
2 3
…
42 43