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)

57
votes
4answers
10k 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 ...
48
votes
4answers
20k 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 ...
2
votes
2answers
2k views

Return counts for multiple ranges in a single SELECT statement

I have a Postgres database table foo that, among other things, has a column for score that ranges from 0 - 10. I want a query to return the total number of scores, the number of scores between 0 and ...
33
votes
7answers
21k 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 ...
3
votes
3answers
4k 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 -- ...
13
votes
3answers
390 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? ...
5
votes
2answers
871 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 ...
5
votes
2answers
5k 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 ...
7
votes
1answer
4k 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[] ...
3
votes
2answers
14k 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 ...
4
votes
2answers
343 views

How can I pass column to function in sql?

I'm calculating median as: DECLARE @TEMP TABLE ( ID INT ) Select ( ( Select Top 1 ID From ( Select Top 50 Percent ID ...
3
votes
1answer
6k 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
3answers
597 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 ...
1
vote
0answers
174 views

Multischema view for postgres , querying multiple databases

Im managing SaaS app where each customer has a separate database (Postgres 9.3) . I need to figure out the best way to aggregate and query this data ( READ-only ). I know postgres has problems with ...
-2
votes
1answer
41 views

why unable to execute table-valued function?

Here is table-value function I have declared like: CREATE FUNCTION fn_GetMedian(@List TypeMedian READONLY) RETURNS INT AS BEGIN RETURN ( Select ( ( ...
26
votes
13answers
69k 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 ...
19
votes
3answers
4k 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(...) = ...
10
votes
3answers
379 views

Combining separate ranges into largest possible contiguous ranges

I'm trying to combine multiple date ranges 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, ...
2
votes
1answer
285 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
423 views

Group activities in activity feed by users and products

On my website I'm building an activity feed where I got the basics done and are now making some improvements here and there. One of them is the grouping. I've built it so that if multiple users buys ...
6
votes
2answers
1k 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
2answers
670 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, ...
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 ...
3
votes
2answers
179 views

Filter and append data to row

My query and my data set (copied from sqlfiddle): CREATE TABLE one ( id int4 primary key, p_id int4, k_id int4 ); CREATE TABLE two( id int4 primary key, p_id int4, k_id int4, ...
3
votes
1answer
218 views

Grouping() equivalent in PostgreSQL?

I have a SQL query that runs in MS SQL Server and it has grouping(), rank() and rollup() functions / keywords in it. The query is given below. How can this be translated to PostgreSQL? select top ...
2
votes
3answers
2k views

Return total number of rows and selected (aggregated) data

I have a function which is selecting some data from a table. I would like to return the selected data and the total number of rows in that table. How can I do that or how can I get the same result in ...
2
votes
1answer
104 views

ORA-00937 when calculating a sub-value in an aggregate select statement

The problem described below applies to Oracle 11g databases. I also tested the statements on MySQL and PostgreSQL with no problems of the kind mentioned in this question. I want to use an ...
1
vote
2answers
200 views

Combine results of two queries, where the second relies on the first

I have one query which is returning name(text), total, created_at And another query which is returning the same thing name(text), total, created_at The second query usually is returning more ...
1
vote
1answer
136 views

Postgres Nested WHEN Aggregate Function

In PostgreSQL (version 9.4) I am trying to construct a query to determine in various tranches how many times various numbers appear in the data set between ranges. When I query group-ing by ...
1
vote
1answer
478 views

Fetch data from same table using two group by clauses in mysql

I have one table name as consultant with 4 fields(id, name,user_type, Created date). I need to write an mysql query for return the count of user_type in each month. In my table, user_type is ...
1
vote
1answer
678 views

Slow queries related to subqueries using aggregation

Here's a simplified version of the queries I am running: select ... from editions join edition_events on edition_events.edition_id = editions.id join events on events.id = ...
-1
votes
2answers
67 views

Calculating multiple medians

I have defined function with table value parameter which returns median as per values passed to it. Function defined as: CREATE FUNCTION [dbo].[fn_GetMedian](@List TypeMedian READONLY) ...