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)

1
vote
1answer
16 views

Oracle How to Generate Multiple Ranges Based on a Result Set

Given a table like this: with qtys as ( select 3 as qty from dual union all select 10 as qty from dual union all select 9 as qty from dual union all select 7 as qty from dual union all ...
1
vote
0answers
43 views

MySQL Get first and last day of multiple records

I have a table HC_RSPI_data: | RADIO_TYPE | HOP | TOWARDS | RSPI | TP_NAME | DATE_TIME_END | TIME_LENGTH | RLTM_min | RLTM_max | ...
1
vote
1answer
168 views

MongoDB Insert Performance

I have some data which are aggregated in chunks and later pushed into another collection. The problem is that aggregated data pushing to another collection is very slow and takes from few hours and i ...
3
votes
1answer
5k 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 ...
5
votes
1answer
126 views

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 ...
2
votes
1answer
64 views

Avoid joining to the same table multiple times

I have two tables: CREATE TABLE one ( id int4 primary key, p_id int4, k_id int4, c_id int4 ); CREATE TABLE two( id int4 primary key, p_id int4, k_id int4, t_id int4, pos int4 ); Table ...
0
votes
1answer
135 views

SQL Server Function For Calculating Average Excluding Outliers

I'm looking for a function that will let me take an average and exclude outliers. Basically something where: 1, 2, 1, 2, 7 Would average out to 1.5. I'm going to implement this in a financial ...
3
votes
2answers
120 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, ...
2
votes
1answer
69 views

Why is ORDER BY LIMIT 1 so much faster than MAX?

Here are my two queries: select sts_in from sta_session where sts_user_id=2006 AND sts_sessid!='0jitkt80gg3avere03tqk4lhi6' order by sts_in desc limit 1; And select max(sts_in) from ...
2
votes
1answer
58 views

How can I get hours worked totaled for a column?

I get good results until I try to perform calculations on an alias column. I am aware that that is not possible. However I need a breakthrough. Can someone lend a hand please :) I have to finish this ...
2
votes
1answer
94 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 ...
4
votes
1answer
40 views

Select a count with some conditions

Using Postgres 9.4, I have a table page_views which has a page column. I'd like to select all the distinct pages and have a count before them and sort them descending. I'd also like to have a minimum ...
0
votes
1answer
21 views

How to use PostgreSQL bit_or function to aggregate column?

I wish to use PostgreSQL's bit_or function on several rows of a single column of my table, but I can't figure out the proper usage for this. Suppose I have bitwise flags in my column flags, and the ...
1
vote
2answers
212 views

PostgreSQL : avoid SUM over same values multiple times

I've a student table and a table of scores in various subjects. I want to sum score1 & score2 for each student across all subjects and then add the bonus for each student. CREATE TABLE student ( ...
1
vote
0answers
26 views

Best way to perform real time analytics on a growing collection of json file ( very large ) [closed]

I have a very large collection of JSON file (tens of millions) on S3 and I need to have some kind of aggregation (perform continuously as the collection grows). The aggregation is mainly counter ...
1
vote
2answers
54 views

Aggregating results of two database views in one database view

I have a view with name of UserInfo like this: And another view with name of UserRoleInfo like this: I want to aggregate all the records of the two views for one user name like H.F into ...
0
votes
2answers
57 views

Count of distinct values of a table using sql

name number ============= lin | 8 lin | 8 lin | 8 rob | 8 rob | 8 rob | 8 lin | 8 lin | 41 lin | 41 rob | 41 and I want the result like name | Count of 8 | count of 41 ...
3
votes
1answer
89 views

Decode and aggregate a BitMask in t-sql

I have a table that contains a bitmask field that stores permissions, where each bit denotes whether a particular permission is granted or not. Here's a simplified example: DECLARE @T TABLE (id ...
0
votes
1answer
22 views

postgresql double count query

I have a table with 3 columns: key, status and rank. status contains numbers and rank contains t or f. key is auto numbered and irrelevant here. my goal is to count how many in each status are t and ...
14
votes
5answers
27k 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. ...
10
votes
3answers
172 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, ...
1
vote
4answers
71 views

Getting an aggregate (here: min) for a group of values [closed]

Example table: ID | value A | value B ---------------------- 1 | abc | 3 1 | def | 5 1 | ghi | 1 2 | cba | 9 2 | fed | 4 I want the ...
1
vote
1answer
15 views

Is it possible to use a column value as the argument to a hypothetical-set aggregate function?

Say I have a table like the one described below. Table "public.foo" Column | Type | Modifiers ...
1
vote
1answer
452 views

Calculating a running balance to store with transactions

I need to put a read-only copy of balance and transaction data from a legacy system, which will only be used to view/print statements. Balance | id | member_id | product_id | balance | | 1 | ...
0
votes
1answer
87 views

Put Postgres query result in array

How can I put an sql query result in a string array with Postgres? I have some attributes in the column 'zone' of the table 'mytable' and I must put them all in a String[] areas; This is my first ...
0
votes
1answer
383 views

Nested query not recognize external table

i need to a query that show week by week the total amount of imports of any company and in addition add a ranking position depending on that amount. I've made this query but the problem is the inner ...
1
vote
2answers
180 views

Calculating percentage from two tables

I have two tables: First one contains unique mentees under one mentor, so mentors can have more than one mentee in that table. Second one contains interactions of mentors with mentees on different ...
1
vote
1answer
75 views

How to get the equivalent of array or string GROUP BY aggregate functions in Access?

From SQL Aggregate Functions (Office 2007) there are only these GROUP BY aggregate functions in Access SQL: Count(), Sum(), Avg(), First(), Last(), Min(), Max(), StDev(), StDevP(), Var(), VarP() ...
0
votes
0answers
12 views

How to calculate sum of a column in a table accessed with multiple joins and where condition

I am unable to find correct sum of vaccinated based on campaign id. no matter which campaign I choose to get sum of vaccinated on the basis of that campaign id it always returns the same data. Please ...
0
votes
1answer
61 views

Optimization for aggregation and grouping with MySQL

I have an application that is pulling data on a daily basis from numerous APIs, storing it and then presenting it on the front end in various charts, tables, etc.. I'm having some issues avoiding ...
1
vote
1answer
61 views

Name for intermediate aggregated data [closed]

We have raw data and aggregated data as well as sometimes multiple levels of aggregation between. Is there a name for the data in that intermediate "state" of aggregation? Just like how we have Real ...
0
votes
1answer
39 views

Restricting multiple records to one by preference?

Below is a query I'm using to extract the best Price1 amongst several distributor's pricing tables. SELECT products.UPC, T2.Price1, vProducts_PriceQty_Union.Price2, ...
1
vote
1answer
137 views

SQL query to sum a column prior to date and show all entries after that date

I have a database function that sum a column prior to a date and after that date show all entries one by one. that function return the data ordered by a common field and apply the sum by date every ...
5
votes
2answers
3k 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 ...
2
votes
1answer
32 views

Search for authors whose records are separated by at least X days

I am using Postgres and have a table which contains metadata about several million images - many of which have the same author. I am looking for contributors who add content regularly - not people ...
2
votes
3answers
139 views

Aggregate query with min

Having a product_stock table with following structure: product_id warehouse_id stock price Which has bunch of records (same product can be in different warehouses): 1, 1, 0, 500 1, 2, 5, 505 1, 3, ...
0
votes
1answer
103 views

Select on multiple tables with group by

Using Postgres 9.3, I have problem with a select over few tables. I have simcards stock and I would like to get something like this: | network | supplier | total topups (EUR) | 1st topup qty| | ...
0
votes
0answers
24 views

parent / child aggregate column

To start with, here is my fiddle. I have a parent / child relationship setup, I have them ordered by who they belong to, and I want to have a column called lastUpdated in which the parent has the ...
1
vote
1answer
96 views

Type with nested table in oracle

I want to create a type with a nested table. I have a table called assignment_tbl which I want to put a type assignment_status. This assignment_status contains primitive types but also contains a ...
19
votes
11answers
55k 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 ...
1
vote
2answers
390 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 ...
0
votes
1answer
117 views

Group by without temporary table and filesort

My aim is to make the following query faster. SELECT SQL_NO_CACHE Links.id,Links.link,sum(ID_Links_Words.count) AS wordCount FROM Links LEFT JOIN ID_Links_Words ON Links.id = ID_Links_Words.link ...
1
vote
1answer
87 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 ...
1
vote
2answers
86 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
2answers
70 views

Combine multiple similar queries into one

I need to calculate a weighted average using the following table. I have to calculate this number for metric_1 up to metric_10. All the metric columns have a value from 1 to 5. ...
0
votes
2answers
26 views

Adding counts inside a COALESCE

So I have a continuation of this question. New requirements have thrown in a third table. So here's the breakdown of how the tables relate: TabA(TabA_id, desc) TabB(TabB_id, TabA_id) TabC(TabC_id, ...
0
votes
1answer
58 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 ...
0
votes
1answer
19 views

Adding a column to aggregated data where new value only applies to a subset of the aggregation

Having trouble explaining this. I have a table like this: Label | Value A | 3 B | 1 C | 4 And another like this: Label | Value | ID A | 1 | ID1 A | 1 | ID2 C ...
1
vote
2answers
180 views

Postgres Index a query with MAX and groupBy

Is there any way to index the following query? SELECT run_id, MAX ( frame ) , MAX ( time ) FROM run.frames_stat GROUP BY run_id; I've tried creating sorted (non-composite) indexes on frame and ...
0
votes
1answer
113 views

SQL Server's aggregate functions ignore nulls. How do I correct this?

According to MS's knowledgebase, Except for COUNT, aggregate functions ignore null values. Does anyone know how I can get it to work correctly, that is, return a null if it attempts to ...