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

40
votes
4answers
3k 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
8k 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 ...
23
votes
7answers
7k 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 ...
5
votes
2answers
473 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 ...
3
votes
2answers
940 views

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 want to have the following view CREATE OR REPLACE VIEW daily_trans ...
13
votes
3answers
323 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? ...
6
votes
2answers
548 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, ...
5
votes
2answers
794 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) , ...
4
votes
1answer
515 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
1k 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 ...