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)

9
votes
3answers
119 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
62 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
109 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 ...
1
vote
1answer
12 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
384 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
38 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
345 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
123 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 ...
0
votes
1answer
52 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 ...
1
vote
1answer
42 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
9 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
37 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
51 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
28 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
70 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 ...
4
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
31 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
134 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
62 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
16 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
93 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 ...
18
votes
11answers
50k 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
167 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
68 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
69 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
70 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
55 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
21 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
40 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
17 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
96 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 ...
-1
votes
1answer
76 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 ...
1
vote
2answers
712 views

LIMIT to one row per ID

This query gives me multiple hits because of each assetid may contain many vb.title. How can I limit the result to only 1 hit for each assetid? 'select DISTINCT v.id, v.purchasedate, ...
1
vote
1answer
66 views

Join and count boolean columns by logical OR

There are two tables: user_table and user_schedule. Each user has four schedule rows (one for each 6 hour period per day) with one column for every workday. The desired result is to count the number ...
0
votes
0answers
45 views

Saving String as unique number in Mongo

I have a field in my collection which is a String in nature. When i run aggregation for this field in data it takes considerable time in aggregation. Total 6 million records are in collection which ...
0
votes
1answer
42 views

HAVING ANY in Postgres

I'm looking for the right syntax to filter groups (i.e. groups of rows that have been GROUP BY'd) where at least one row fits a certain condition. For example if I wanted to select a group where at ...
0
votes
0answers
48 views

index of aggregate value with fast update?

Is it possible for me to index the sum of a particular table column, such that future lookups will not have to scan the entire table? This table will grow to be very large and a sum of the entire ...
0
votes
3answers
545 views

Getting around the constraint “column must appear in the GROUP BY clause or be used in an aggregate function”

I'm using Postgres, which enforces the constraint that all columns in a SELECT...GROUP BY must appear in the GROUP BY clause or be used in an aggregate function. Lets say I'm modelling peoples' cars, ...
1
vote
1answer
52 views

How to properly aggregate?

I have a table for logs with over 6 million records. I want a query that shows me all the occurrences where the src goes to the same destination/port. I tried this: SELECT src, dst, dstport, ...
1
vote
1answer
2k views

Postgres error [column must appear in the GROUP BY clause or be used in an aggregate function] when sub query is used

I have two tables employee and phones. An employee can have 0 to n phone numbers. I want to list the employee names with their phone numbers. I am using the below query which runs fine. SELECT ...
1
vote
2answers
127 views

Understanding multiple table join with aggregation

I have a basic question on how JOIN works on multiple tables. I want to count occurrences of Foreign Key in link1 & link2 CREATE TABLE main ( id SERIAL PRIMARY KEY, name text NOT NULL ); ...
-1
votes
1answer
220 views

Sum of two columns from pivot

I have the following query: DECLARE @columns NVARCHAR(MAX),@columns1 NVARCHAR(MAX), @sql NVARCHAR(MAX),@columns2 NVARCHAR(MAX),@columns3 NVARCHAR(MAX); SET @columns = '1,2,3,4'; SELECT @columns1 = ...
3
votes
2answers
63 views

Selecting minimum value in top grouping only

I have a query built in Oracle 10 which produces a set of data like this: EMP_ID WORK_DT TIME_TYPE ---------- --------- --------- 12345 19-DEC-14 10 12345 18-DEC-14 10 ...
0
votes
2answers
98 views

Aggregating sum day by day from multiple joins returns unexpected results

I'm trying to make a table where the task hours and group hours are aggregated day by day. I'm able to get the wanted result for both tasks and groups, but when I try to get them in the same query, I ...
3
votes
3answers
3k 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 -- ...
0
votes
0answers
64 views

aggregate array records in mongo db

HI All I have this Data in mongo, {"articleId" : [ { "articleId" : "9514666", "articleCount" : 1 } ], "count" : NumberLong(1), ...
2
votes
3answers
463 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 ...
1
vote
0answers
98 views

Pentaho Kettle (Data Intergration) MongoDB Aggregation

I'm using kettle v5.2 which support the aggregation pipleline in MongoDB when using MongoDB input the query works for small data set but I need to use option allowDiskUse to the query can't figure ...
4
votes
1answer
771 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 ...
8
votes
5answers
7k 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? ...