Questions tagged [aggregate]

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.

68 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
2
votes
1answer
148 views

merging many jsonb objects using aggregate functions in PostgreSQL?

Is there a standard function in PostgreSQL (as of 12.x) to concatenate or merge many jsonb objects in a database column into a single jsonb object? I know there is a the || operator since PostgreSQL ...
2
votes
0answers
43 views

Relational Algebra: how to express the combination of aggregation and projection?

Here I have a table, I would like to query the average salary of departments, the SQL statement should be select dept_name, avg(salary) from employee I try to express this SQL in relational algebra....
2
votes
0answers
55 views

Recommendations around managing large amounts of data that needs to be aggregated (SUM)

I am looking for alternatives to redesign a part of our system that needs to perform cumulative SUMs over large data sets of rows based on user and dates. How the system works We are running a web (...
2
votes
1answer
236 views

Does bool_and short-circuit function calls with arguments?

Suppose I have the following query: SELECT a.* FROM a CROSS JOIN b GROUP BY a.id -- Primary key HAVING bool_and(expensive_comparison(a, b)) What will PostgreSQL do with this query? Will it process ...
2
votes
2answers
2k views

Time series data - question for daily aggregation query

I'm trying to query a MySQL database with hourly timeseries data but I'm stuck with (daily) aggregation. My raw data: mysql> SELECT * FROM emoncms.custom_registers -> WHERE timestamp >=...
2
votes
1answer
133 views

Postgres - Querying Min, Max and getting associated columns data specific to those result of those rows

I have query in which returns averaged values by site and month. What I want to know is the min and max of those monthly averages by site and (the difficult part) the month when each occurred. Here ...
2
votes
0answers
428 views

MySQL aggregation count performance

I have a table with about 30 million records, and I'm doing a query where I can group by field Year and count the results for that year. The table structure looks like: id | year | amount The query ...
2
votes
0answers
1k views

How to calculate running total based on dynamic values?

I need to calculate the running total in which the target column is changing along with the time. Its something which aws does. Charging based for only what you use. So how do I aggregate sum of ...
2
votes
1answer
87 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
28 views

Postgres HAVING ilike any wildcard in array_agg

I need a filter where the input value is similar to any value in an aggregate, using ilike and wildcards. My attempt at a query so far is: SELECT p.id, p.name, array_agg(vo.name) FROM product p LEFT ...
1
vote
0answers
58 views

Group by equidistant integer intervals

I am using TimescaleDB (an extension to PostgreSQL) to store altitude data from a GPS sensor. The table looks like: CREATE TABLE IF NOT EXISTS altitude ( time TIMESTAMPTZ NOT ...
1
vote
1answer
37 views

Error in update. I did not find the error. Why is max(C) not found?

(Translated from Portuguese with Google Translate) I created a table and made an update that should update column B equally to the percentage that C represents in relation to the maximum value of ...
1
vote
0answers
48 views

Joining on nearest timestamp in MongoDB

MongoDB version: 3.2.11 Coming from using primarily PostgreSQL, I am new to MongoDB and am having trouble constructing complex queries in the required JSON format (where in SQL I would use CTEs). To ...
1
vote
1answer
72 views

How to get aggregate data from a dynamic number of related rows in adjacent table

EDIT: Unknowing of the rule that prohibits cross-posting, I also asked this on Stackoverflow and chose an answer over there. Since there's another (fully working) answer in this thread though, I won't ...
1
vote
0answers
137 views

Poor UnionAggregate (CLR aggregate) performance - CPU cost hint?

I'm trying to use SQL Server Spatial to work with data related to geographic regions defined by a third party, and am having trouble getting acceptable performance. The following query takes quite a ...
1
vote
0answers
58 views

Combining two event tables into one postgres view

I have two tables, events and event_logs. The events table stores the theoretical data on a recurring event whereas the event_log stores specific information on a particular datetime. Event +-----...
1
vote
0answers
115 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
0answers
702 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 ...
1
vote
0answers
278 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 ...
1
vote
0answers
132 views

Join and aggregate result and insert blanks for missing appendants

Consider these two simplified tables: CREATE TABLE "sources" ( "id" serial NOT NULL, "name" text NOT NULL, CONSTRAINT "sources_pkey" PRIMARY KEY ("id") ); CREATE TABLE "values" ( "...
0
votes
0answers
67 views

How to change array_agg() to return empty array on empty input in PostgreSQL?

I am trying to write a PostgreSQL array_agg() variant that returns the empty array '{}' instead of NULL. Trying to avoid the performance hit of custom aggregate functions with CREATE AGGRGATE, I tried ...
0
votes
0answers
7 views

MongoDB Charts Aggregation Pipeline

does anyone know if it is possible to use a fully fledged aggregation pipeline to a MongoDB Chart (cloud hosted charts)? There is a placeholder for the pipeline but it won't parse my projection, ...
0
votes
0answers
19 views

Finding matching suggestions with multiple records from different “users”

I'm not even entirely sure how to phrase my question because it is rather complicated. I am trying to implement a feature that will help crowd source information for one of our systems, and the system ...
0
votes
1answer
24 views

Aggregating data by group and time gaps

I've got a postgresql+postgis database filled with GPS data: CREATE TABLE "my_table" ( "id" serial NOT NULL PRIMARY KEY, "gps_id" text NOT NULL, "lat" numeric NOT NULL, "lon" ...
0
votes
0answers
58 views

MongoDB - Update all documents NOT returned in aggregate query

I'm using PyMongo to write a Mongo update script. I have an aggregate query which returns a sum of all searches performed by each user in the last 30 days: cursor = mongo_search_logs_collection....
0
votes
0answers
10 views

How can I merge array of arrays in $project stage?

I merge 2 collections into one mother: { _id: ObjectId(), address: "something" } child: { _id: ObjectId(), name: "something", contacts: [{ "phone": "+144543823..." }, { ... }] } ...
0
votes
2answers
37 views

Index conditional aggregate query on PostgreSQL

How does one optimize a conditional aggregate query on PostgreSQL? A example of such query would be: SELECT SUM(score) FROM "games" WHERE "PlayerId" = 23 AND "status" = 'FINAL'; Indexing (PlayerId, ...
0
votes
0answers
40 views

How to compress a query to get distinct COUNT and SUM aggregate information on joined rows with sub-queries in Microsoft SQL

I have a query that counts some incidents that happen based on joining some groups of companies and regions. SELECT RC.RegionId, RC.CompanyId, CC.Name, Quantity = SUM(CASE WHEN II....
0
votes
0answers
57 views

Getting View of One to Many where Column Names are repeated with Indexed field

I have 4 tables joined with an inner join. 2 of these joined tables are 1 to Many relationships. Consider the following example return where t1 is table 1, t2 is table 2 and so forth. table 1 has a 1 ...
0
votes
0answers
41 views

query start, due, complete dates for first and last time phases from phase tracking table

linked excel file I have a phase tracking table which consists of the fields record id, phase name, record order number etc: RECORD_ID,phase,RECORD_ORDER,SUBMITTED_DATE,DUE_DATE,COMPLETED_DATE 1657,...
0
votes
0answers
80 views

How to $lookup embedded documents in MongoDB aggregation pipeline?

I have 3 collections each related to the other. Let's assume that first collection is University: { _id: ObjectId(), name: '', description: '', creation_date: '', address: '' } And Teacher ...
0
votes
0answers
40 views

Aggregation in mongodb and group by 15 min

I have a Schema in mongodb like: INPUT { _id: ObjectId("5e05c1089b3e4e333cee8c39"), name:"Alex", activity:[ { _id: ObjectId("5e05c1089b3e4e333cee8c39"), type: '...
0
votes
0answers
31 views

Google BigQuery - Using ARRAY_AGG and Aliasing Fields

I am aggregating various tables into a singular "mega" table to drive an analytics platform. While I have several WITH clauses defined above my provided code snippet, these are simply to mitigate ...
0
votes
0answers
66 views

Force timeout on MongoTemplate aggregate API

Is there any way to add time out parameter maxTimeMs in the below query? I want the aggregation to time out after 1 minutes List<AggregationOperation> aggregationOperations = ...
0
votes
0answers
13 views

Can't do sum aggregate on Tweet Api

I'm new using mongodb and using the function .find() to search documents. Recently I've been working with the tweeter API and I have to do some "queries" regarding the results of my requests. This ...
0
votes
0answers
160 views

Comparisons between average and raw weather data

I have a problem calculating some averages against raw data. My data comes from a weather monitoring system. My test set is 19.2 million rows, but for now I am attaching a db<>fiddle with around ...
0
votes
0answers
16 views

Return Value Corresponding to Latest Date (SQL)

Let's say that I have these tables: Widgets ID Widget 1 W1 2 W2 Widget_Making ID Person_Involved ID_Widgets 1 John 1 2 James 1 3 John 2 ...
0
votes
1answer
38 views

Multiple vs Single COUNT on same col + Multiple vs Single col index

I have two very simple questions 1. Multiple vs Single COUNT on same col I need to perform CASE on an aggregate column like this: SELECT tbl_students.school, COUNT(tbl_students.id) as students, ( ...
0
votes
1answer
28 views

Get 2 counts in single query

I need to get count of 2 columns with group by This is my sample data I need to get count of persons from particular state and count of persons from particular country My output should be like I ...
0
votes
0answers
19 views

select rows and calculate number of rows based on parent column value

I have a total of three tables with a many-to-many relationship. 1.) users id | email ----------- 1 | usera@example.com 2 | userb@example.com 3 | userc@example.com 2.) accounts id | userId | ...
0
votes
0answers
63 views

Variable Records

This question keeps coming up at work & collectively we can't think of an easier way to do it than a SQL pivot & huge case statement. So I'm hoping someone might have an idea that could help. ...
0
votes
0answers
77 views

How to get records from one collection based on role hierarchy assigned from another collection in mongodb using $graphLookup aggregation

I have a roles collection whose schema is like this : { name:String, parent:{type:Schema.Types.ObjectId, ref:"role"} } it has 3 documents right now as shown below { "_id": "...
0
votes
0answers
36 views

User-defined harmonic mean function performs worse than query in PostgreSQL 9.6

We created a harmonic mean function based on this blog post: CREATE OR REPLACE FUNCTION public.harmean_accum(numeric[], numeric) RETURNS numeric[] LANGUAGE sql STABLE COST 1 AS $h$ SELECT array[$1[1]+...
0
votes
0answers
19 views

Postgres count the same join by different values

I've been trying to create a query that counts the number of different types of events associated with each entry in another table city job job_events id | name | state ...
0
votes
1answer
123 views

Slow performance of query with $and & $or operators

My query with $and, $or operators is performing poorly. Even though I've built a compound index on all three fields that are specified in my query criteria, the query does a complete collection scan. ...
0
votes
1answer
26 views

How to summate weekly totals from a postgres date range type per user

I have the following table: Schedules +----+---------+------------+-------+--------------------------+ | id | user_id | project_id | hours | date_range | +----+---------+------------+---...
0
votes
0answers
172 views

MySQL How to get first row from subquery when using aggregate function

This is my Query: SELECT COUNT(runs_scored) - SUM(IF(extra_type_id = 4, 1, 0)) AS Balls, SUM(runs_scored) + SUM(IF(extra_type_id IN (2 , 4), extra_runs, 0)) AS Runs, SUM(...
0
votes
1answer
375 views

Calculate Count Distinct without grouped columns in Oracle

I am trying to extract a few records and I ran into the "not a single group" error. This error can be resolved by removing the (count(distinct dc.DUCT_FID)) as NO_OF_WAYS column expression. However, I ...
0
votes
2answers
188 views

sum only different id's values with any aggreagte function in postgresql

I need your help. Let's assume that we have a table like below +---------------+-------+--------+----------+ | barcode | shop | id | entrance | +---------------+-------+--------+----------+...
0
votes
1answer
48 views

How to check if list of values in one column match list of values in another column (aggregations)

Given two results consisting of single key|value pair (you can imagine those are in CTEs), I want to join and group them by key, aggregate their values and return two different things: a) those keys ...