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.

Filter by
Sorted by
Tagged with
0
votes
0answers
34 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
39 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,...
-2
votes
1answer
27 views

Modify table by squashing rows by truncated hour

I have a table ledger which represents the ledger book. date | user_id | usd_value | eur_value ----------------------------+---------+-----------+----------- 2020-01-13 19:00:...
0
votes
0answers
12 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
20 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
6 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
11 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 = ...
5
votes
3answers
40 views

Allow for dynamic result sets from plpgsql function by filtering based on an array passed in?

I think I've avoided the XY problem here, as I'm laying out my solutions to the real underlying problem (summarizing multiple tables in a dynamic fashion) and I'm only asking about the one final part ...
0
votes
0answers
12 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 ...
1
vote
1answer
35 views

Postgres: how to limit the number of query results by aggregate type

Consider the following table definition CREATE TABLE associations ( id SERIAL PRIMARY KEY, resource_id integer, target_id integer, relation_id integer ); which is used for specifying ...
0
votes
1answer
26 views

How to fold an event stream into a table?

I'm trying to experiment a pure-postgres event sourced projection, and I'd like to represent a stream of events as a table: imagine this flow of events: ┌───────┬───────────────────────┬───────┬─────...
0
votes
0answers
18 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 ...
0
votes
2answers
23 views

Work around average behaviour with nulls

I have two tables: Product (PK product_id, brand, model) Order (PK invoice_id, customer_name, customer_email, FK product_id) I am trying to find the product(s) whose number of times that product is ...
0
votes
1answer
32 views

Filter based on values after join operation - MongoDB

I have two collections in the following format - collection1: { "_id": "col1id1", "name": "col1doc1", "properties": [ "<_id1>", "<_id2>", "<_id3>"] } collection2: { ...
0
votes
1answer
46 views

Adding a subquery to my SELECT clause returns an error

I have a query which was originally written to retrieve work-event data based on whether the work-event was updated during a specified date range. The query ends up being used to populate a reporting ...
0
votes
1answer
18 views

MongodB cumulated conditionnal array:using map/reduce or aggregation

First, I aplogized because I have some problem to formalized my question so any idea on express it correctly will be appreciated and edited. Last try: How to get last of a cumulated conditionnal ...
3
votes
2answers
33 views

Rolling sum aggregates with self-referencing conditions

I have what I believe is a use-case for a recursive CTE, but I've yet to figure out how to structure it. The requirement is to create a rolling sum total over an amortization schedule, but payments ...
0
votes
1answer
33 views

percentage of count in Oracle

I'm trying to get percentage of payments according to status every day. I found a MySQL query which throws an error at Oracle when I use '=' inside SUM parentheses: "ORA-00907: missing right ...
0
votes
0answers
151 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 ...
-1
votes
2answers
31 views

SQL: Select column in group by based on another column's aggrigate

Say I have the table of the following format UID | A | B ---------------- 1 2 1 2 5 1 3 4 1 4 3 2 5 8 2 6 7 2 7 4 3 8 1 ...
-1
votes
2answers
46 views

Add Grand Total end of the row for each customer

I need to get Total for each Customer I have a table like this. Create table mytbl (Branch varchar(10), CustCode varchar(10), Name varchar(10), Address varchar(10), ...
4
votes
1answer
41 views

How can we handle out-of-range errors when aggregating data?

For example we have some data in int8 fields and want to build aggregations: e.g. the sum of squares. The result should also be int8 (not a float). So obviously calculating the sum of squares for ...
0
votes
1answer
84 views

Calculate 12 months rolling / moving average, median, min, max, percentiles, etc. as single query in Postgres

My question has two parts: . Firstly, how to adjust the code below to calculate 12 month moving avg, median etc.? As is, the query will return a list of daily, weekly, monthly or annual values (...
-1
votes
1answer
30 views

Unable to declare user-defined variable in MySQL

I'm creating procedure called min_max to find minimum and maximum for every column in a table. But I'm encountering an error for declaring the variable. See below: DELIMITER // DROP PROCEDURE IF ...
2
votes
1answer
31 views

Build a single JSON value from two jsonb columns in two different tables

I have two tables. Both have a jsonb column with an array of objects, each containing the key student_id. I am trying to build one aggregated JSON value from these jsonb columns. Lets say table A ...
0
votes
0answers
12 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
37 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, ( ...
5
votes
1answer
81 views

Grouping by overlapping arrays, transitively, without duplicates

I've found: Group by array overlapping https://stackoverflow.com/a/23650080/5419599 However, I'm having trouble putting it to use in my case. I have a table like so (the real myid values are hashes,...
0
votes
1answer
23 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
2answers
35 views

How to do a cumulative count for certain values

I have a scenario to do cumulative counts in certain way. So I decided to create an experiment for that before I run the query in the actual environment. I have created a table as: create table ...
1
vote
2answers
30 views

Aggregate results from select queries

I have a table with three columns: id, date, state. For each unique value of id, I would like to select date and state, multiply the state array by a scalar variable for each id defined from else, ...
0
votes
0answers
17 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. ...
1
vote
1answer
29 views

Using aggregate function in custom function

I need to calculate the rms (root mean square) for different columns. To not repeat myself I would like to create a custom aggregate function rms(numeric) which should do the following calculation: ...
2
votes
1answer
29 views

Problem filtering information in MySQL query

I've a little problem with a SQL query. The objective is to get an employee profile related to a role. Table EMPLOYEE (Louis,Mary,Christian): EmpId EmpName Table ROLE (Chef,Waiter,NULL): RoleID ...
0
votes
0answers
36 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
1answer
26 views

Cumulative array aggregation in Postgres

Does anybody know if it is possible perform cumulative array aggregation in Postgres? For example, to transform this: create table records ( data text, path text[] ); insert into records values (...
0
votes
1answer
76 views

Sum of values each hour at half past (that hour)

I need to aggregate the "sum" of values of a table with TS, Value columns of each half past hour (of 60 minutes), i.e. 00:30, 01:30, 02:30, etc Sample data: Val TS 1 2019-08-12 00:00:00.013 3 ...
2
votes
0answers
32 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....
0
votes
1answer
18 views

How to rank an aggregation in each newly formed group?

The problem below that I would like to solve sounds quite simple but it is quite difficult for me to arrive at the solution with SQL. Let's say I have a simplified ERD that looks as follows: This ...
1
vote
3answers
65 views

Take average of multiple nullable columns

I have an example table (year) as below in PostgreSQL 9.5: Name | 2010 | 2011 | 2012 ------------------------- A | 10 | | 40 B | 10 | 20 | 30 Now, if I write a simple query as ...
-1
votes
2answers
780 views

join two collections with group by and sum in Mongodb

i have this two collection and i want to join, after this I want to sum the value of TotalPrice each orderdate : some line of my two collections the first collection date : {"Id_Date":1,"...
0
votes
0answers
28 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
3answers
94 views

SELECT minimum value without a subquery

I have this table: auctionid bid bidtime bidder bidderrate openbid price 8211851222 55 6.021053 smoothy43m 69 50 161 8211851222 51 6.378843 ahmadf316 ...
0
votes
1answer
27 views

How to aggregate results from SELECT JOIN

I have two tables: **user Table ** id username --- ----------- 100 greg 101 john 102 patrick 103 bill **user_subscription_plan Table ** id user_id amount --- --------- ------- 1 ...
10
votes
1answer
179 views

Hash aggregate bailout

A question that arose in a chat discussion: I know hash join bailout switches internally to a sort of nested loops thing. What does SQL Server do for a hash aggregate bailout (if it can happen at ...
1
vote
0answers
57 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 ...
0
votes
0answers
21 views

How to query Groupy by and count with lookup in Mongodb?

I am trying To use group by in a collection and COUNT in another collection. I have 2 collections. One for customer and other for order_fact. Example Sample data for collection customer. {"Id_Cust":...
0
votes
1answer
18 views

three tables relation order is correct but values are false

i have those tables table1 | id | name | | 1 | axe | | 2 | bow | | 3 | car | | 4 | dart | table2 table3 | t1_id | ...
1
vote
3answers
61 views

Is there a way to get count based on multiple columns but still have it be distinct in SQL?

I have included two pictures that are in Excel just for ease of viewing but I am doing the queries in SQL. Hopefully, this is possible as the algorithm I have is not giving me the correct results.