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.

learn more… | top users | synonyms (1)

47
votes
4answers
5k views

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 ...
41
votes
4answers
10k views

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 ...
27
votes
7answers
10k views

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 ...
17
votes
10answers
38k views

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 ...
17
votes
3answers
3k views

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(...) = ...
16
votes
5answers
11k views

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 ...
13
votes
3answers
350 views

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? ...
10
votes
4answers
1k views

How can I write windowing query which sums a column to create discrete buckets?

I have a table which includes a column of decimal values, such as this: id value size -- ----- ---- 1 100 .02 2 99 .38 3 98 .13 4 97 .35 5 96 .15 6 95 .57 7 94 .25 ...
10
votes
3answers
5k views

Why is an aggregate query significantly faster with a GROUP BY clause than without one?

I'm just curious why an aggregate query runs so much faster with a GROUP BY clause than without one. For example, this query takes almost 10 seconds to run SELECT MIN(CreatedDate) FROM MyTable WHERE ...
9
votes
5answers
15k views

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. ...
7
votes
3answers
13k views

Calculating percentage of a row over total sum

Apologies for the bad title, I wasn't sure what would be a good title for this. This is currently (simplified view of the) data I'm working with Agent | Commission ---------|------------ ...
7
votes
1answer
112 views

Trouble with SQL and aggregates

With this query: SELECT SUM(qty) AS sumQty, cnetprodid FROM quoteitem WHERE ordered = 1 AND sageSOPOrderReturnLineID IS NOT NULL AND LEN(LTRIM(RTRIM(cnetprodid))) > 0 ...
6
votes
4answers
475 views

Find the first gap in an aggregation of integers in SQL Server

Let's say I have a table called dbo.GroupAssignment. GroupID | Rank ------------------ 1 1 1 2 1 3 2 1 2 3 2 4 3 2 3 4 3 5 The PK is GroupID, Rank. Normally, the ranks ...
6
votes
2answers
592 views

Most efficient way to return multiple aggregates in a single stored proc?

What is the best or most efficient way to get multiple aggregate result values? Basically I have an email app and wanted to get all the numbers of messages for each type of folder (inbox, sent, ...
6
votes
2answers
978 views

Using totals on aggregates to improve performance

I have two tables: details and totals of these details. Details (Slow solution): select OrderId = r.OrderId , TotalQty = SUM(r.Quantity) , ...
6
votes
3answers
297 views

Is using SUM() twice suboptimal?

I know I have to write SUM twice, if I wish to use it in a HAVING clause (or use a derived table otherwise): SELECT id, sum(hours) AS totalhours FROM mytable GROUP BY id HAVING sum(hours) ...
5
votes
3answers
844 views

Many-to-Too-Many Relationship

I'm working on a book database. And among others, I have the following two tables (a bit simplified for the question): tblBook: ID, Title tblPerson: ID, Name Note that the tblPerson table contains ...
5
votes
5answers
1k views

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? ...
5
votes
1answer
2k views

Oracle aggregate functions slows query down massively

I have a table with ~12M rows of data in it. Here is the table structure: SYSTEM_ID BATCH_ID MEASUREMENT_INDEX, PARAMETER_ONE, PARAMETER_TWO The primary key is made up of the first three columns. I ...
5
votes
2answers
573 views

Is it a good idea to split MySQL DB into two servers

I have a MySQL DB server that accepts a lot of mobile application data for analysis. I would like to know if for optimization reasons the following architecture makes sense and is appropriate: I ...
4
votes
2answers
502 views

MAX for each subset

I have a table that looks like this: +---------------------------------------------+ | EVENT_ID | ITEM_ID | PERSON_ID | EVENT_DATE | +---------------------------------------------+ | 123 | 1 ...
4
votes
2answers
2k views

PostgreSQL query with max and min date plus associated id per row

I have the following table: CREATE TABLE trans ( id SERIAL PRIMARY KEY, trans_date date, trans_time time ); I want to have the following view CREATE OR REPLACE VIEW daily_trans ...
4
votes
1answer
2k views

How do I horizontally partition an oracle database table, and should I?

We have a tenanted data warehouse that we are doing reporting on. Queries are beginning to take a long time, and we're looking at options to reduce this. There are two thoughts at the moment. Create ...
4
votes
2answers
236 views

MySQL aggregate anomaly

I was checking selectivity of some columns for an index. Where is this "ignore what I give you" behaviour documented? This gives 4,851,908, 4,841,060, and 1,000,052 SELECT COUNT(*), ...
4
votes
1answer
1k views

How do I get the aggregate of a window function in Postgres?

I have a table containing a two columns of permutations/combinations of integer arrays, and a third column containing a value, like so: CREATE TABLE foo ( perm integer[] NOT NULL, combo integer[] ...
4
votes
1answer
134 views

GROUP BY on a dependent field

Let's say I have a SELECT statement as follows: SELECT ..., field1 FROM ... GROUP BY field1 Now let's say I want to return a second field, field2. As it happens, field2 is functionally dependent ...
4
votes
1answer
492 views

Is it possible to wrap aggregate functions in Postgres?

Postgres' string_agg(expr, delimiter) function is great. But I would like to have a version that takes a single argument -- the field to aggregate -- and assumes a delimiter of ', ' since that is ...
4
votes
1answer
232 views

Is there a First() aggregate function in postgresql that returns the exact same type as the input?

Full question re-write I'm looking a First() aggregate function. Here I found something that almost works: CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS anyelement ...
3
votes
2answers
473 views

Pivoting data for stacked charts (grouping and creating new columns for each distinct value)?

I have something that looks like this (SQL Fiddle here): Date Location Value 1/1/2012 Baltimore 36 1/1/2012 Houston 36 1/1/2012 Chicago 55 2/1/2012 Baltimore 49 ...
3
votes
2answers
529 views

Returning empty string when string_agg has no records

I am trying to return a text field in a PostgreSQL query that is of the form 'stringOne' || string_agg(field, ',') || 'stringTwo' where for certain elements in the group clause, field is always ...
3
votes
1answer
128 views

Aggregating statistics from child rows

I have a parent row and then many child rows, and I must aggregate statistics from these child rows. For a more concrete example let's imagine I have one round of golf (parent row) and then child ...
3
votes
2answers
7k views

SQL query that concatenate values from duplicate rows in a single table

Lets say I have a table of employees, like this: Name Sex Role Bob M Developer Joe M QA Now, I have a problem with this table of duplicated rows. I will fix it ...
3
votes
1answer
396 views

How to represent aggregates in a data warehouse

I am building the dimensional model for a data warehouse (as an exercise for a mini-course I am doing) and I want to build an aggregate to speed up queries. Basically, I want the aggregate to group ...
2
votes
3answers
289 views

Non-deterministic aggregated result

Why is my query non-deterministic? I have query that do the following: select sum(float1*float2*coalesce(float3,1)) from table When I run this query I get a deterministic result, but it seems not ...
2
votes
3answers
345 views

Why MAX() of SUM() giving wrong output?

I have a table employee id name salary city 1 ram 50000 c1 2 sham 20000 c2 3 jadu 80000 c1 4 madhu 90000 c4 5 hari 10000 c2 6 gopal 34000 c3 7 ...
2
votes
2answers
2k views

How do I create a user-defined aggregate function?

I need an aggregate function that MySQL doesn't provide. I would like it to be in MySQL's flavor of SQL (that is, not in C). How do I do this? What I'm stuck on is creating an aggregate function -- ...
2
votes
1answer
4k views

Select first row (grouping) + add aggregate function

First have a look at this question on StackOverflow. I'm looking to accomplish the same task, except I also need to add an aggregate function (PostGIS's ST_Union) to my query. How can I combine the ...
2
votes
1answer
72 views

PostgreSQL Division In Query Not Working

So I am having a really off day or something, but for the life of me I can not figure this out. I am trying to divide tier1 by total to get a percentage of the total. I thought like this: (tier1 / ...
2
votes
1answer
70 views

What is the appropriate SQL method for “Safe to SUM”?

In SQL Server 2008 R2+ we, unfortunately, have some nvarchar columns storing numeric data. In some cases we want to SUM the data when it can be summed. The problem is that ISNUMERIC() returns 1 for a ...
2
votes
3answers
90 views

Rolling count of total transactions over time

I need to get a set of total transactions over time on a weekly interval. Essentially I need a total-transactions-to-date column. When I group by WEEK(Date) I get the amount of transactions for that ...
2
votes
1answer
138 views

Select data based on records created_at column

I have two tables Keywords, ProjectReports: http://sqlfiddle.com/#!15/06ae3/1 As you can see in that example everything works fine, but I want to get more data from those 2 tables and I don't know ...
2
votes
1answer
631 views

Calculate percentage of false records to total records within a aggregate function

I'm looking for a solution to calculate a percentage rate of a boolean value: Model: Products 1 <-> n Components Components.essential is a boolean attribute What I want to calculate ...
2
votes
1answer
544 views

Is it possible to reduce a group to a row based on some criteria?

I'd like to perform a select on a table involving a GROUP BY such that all rows that share the same set of identifiers are grouped together, but I want to reduce the group to one of the grouped rows ...
2
votes
1answer
352 views

I have multiple sources INSERTing into a MySQL innodb table. My periodic aggregation script never makes accurate aggregates. Why?

I apologize in advance if this is a repeat. I'm not really sure how to properly ask for what I'm running into. I have a large InnoDB table set up. I have 3 sources that all INSERT concurrently at a ...
2
votes
1answer
243 views

Is it possible to make a reference to the result of an aggregate function in a SELECT clause from the same SELECT clause?

I'm not a Database Administrator--just a Software Engineer. I would like to know if it is possible to reduce the following T-SQL query: SELECT SUM(Price * Quantity) as 'Total', SUM(Price * ...
2
votes
1answer
685 views

Why use custom CLR aggregate functions for string concatenation and geometry unions in SQL Server?

I often need to do string concatenation or geometry unions over a column in SQL Server 2008 and I'm aware that you can write custom aggregate functions in .NET and register them with SQL Server to do ...
2
votes
1answer
260 views

How can I use running total aggregates in a query to output financial accumulations?

I am attempting to write a query that will be consumed by a SSRS 2012 report. Given a paid invoice, with a certain set of account allocations towards which the money paid towards it must be saved, I ...
2
votes
1answer
556 views

Performing aggregations and calculations: SQL (CTE) vs MDX

I’ve hit a wall and would like some expert advice. I’m building a data warehouse and don’t quite understand whether the calculations (also time aggregations) should be done in SQL (T-SQL and CTE) or ...
1
vote
2answers
6k views

how to calculate difference of first row and last row fields value in each group

I have table with structure like this: +-------+------------------+ | Value | Date | +-------+------------------+ | 10 | 10/10/2010 10:00 | | 11 | 10/10/2010 10:15 | | 15 | ...
1
vote
3answers
2k views

Select query to retrieve values even if are null

I need some help in a query. Need to retrieve data from 1 table, even if one of the field has null value. Here's an example: Select name, SUM(credit) as credit From expenses Where name like ...