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)

0
votes
0answers
46 views

postgresql view [duplicate]

Possible Duplicate: postgresql view with max min with id I have the following table: CREATE TABLE trans ( id SERIAL PRIMARY KEY, trans_date date, trans_time time ); I ...
6
votes
4answers
479 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 ...
4
votes
2answers
237 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(*), ...
28
votes
7answers
11k 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 ...
2
votes
3answers
297 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 ...
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 ...
5
votes
2answers
590 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 ...
6
votes
2answers
995 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) , ...
9
votes
5answers
16k 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. ...
6
votes
2answers
599 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, ...
41
votes
4answers
11k 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 ...
4
votes
2answers
525 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
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 ...
17
votes
10answers
39k 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 ...
7
votes
3answers
14k 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 ---------|------------ ...
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 ...