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
1 answer
18 views

Postgresql - how to use bool_and with group by

I have the following schema, that represent collection of messages, where each message can have multiple items (at least 1). Every item belongs to a message. Items can change over time. CREATE TABLE ...
user avatar
  • 147
0 votes
1 answer
17 views

Why parallel aggregation is not used for subquery when other subquery using non parallel aggregation?

I defined 2 equivalent aggregates. First supports parallel mode, but second one doesn't. I defined 2 identical tables with index. create table jsfield(j jsonb); insert into jsfield select ...
user avatar
0 votes
0 answers
22 views

SQL Aggregation by Hour

I have an SQLite database (version 3.31.1) for my electricity usage and generation. It has over 100,000 rows, and about 600 days. The "StartTime" field contains the start time of the bucket ...
user avatar
0 votes
1 answer
21 views

Join variable number of rows in Postgres into json array

I have the following table: timestamp | type | value | source ----------+------+-------+-------- 2020-01-01| 1 | 10 | 2 2020-01-01| 2 | 20 | 2 2020-01-02| 1 | 5 | 4 2020-01-02| 2 ...
user avatar
  • 103
1 vote
1 answer
64 views

Cumulative sum by day with non-negative floor with PostgreSQL

Using PostgreSQL 12, I need to calculate the cumulative sum by day while filling in missing days. I also need to set a minimum value of 0 if any cumulative sum goes negative. Background There are two ...
user avatar
  • 111
3 votes
1 answer
134 views

How to transpose values from one column into columns with a value from a different column?

I have a database with the following structure: Date role type duration 2022-04-16 Nurse Food preparation 45 2022-04-17 Nurse Cleaning 30 2022-04-17 Volunteer Cleaning 20 2022-04-17 Nurse Food ...
user avatar
0 votes
1 answer
29 views

MongoDB aggregation. Trying to use switch in map

Hi im trying to use a switch statement inside of map to increment a value depending on the case but im getting MongoServerError: $switch found an unknown argument: $$favcast is this viable? db....
user avatar
0 votes
2 answers
35 views

Get count of series/groups based on 14 days of interval

I have a table containing range table like this. +---------+---------+ | user_id | view_dt | +---------+---------+ | A | 1/1 | +---------+---------+ | A | 1/10 | +---------+---------...
user avatar
2 votes
0 answers
40 views

How to map child (leaf-level) nodes to specific parents with/without ltree for aggregations?

What would be the best way to aggregate across hierarchies? I am using Postgres and have the following 3 tables (folder, folder_hierarchies, and work) - folder Column Type Description folder_id (pk)...
user avatar
0 votes
1 answer
28 views

Select entries that appear more than 4 times

I have 2 tables, one contains people and another contains enrolments with a reference to the people id's. I have to create a view where only results that appear in the enrolments more than 4 times are ...
user avatar
0 votes
0 answers
34 views

Rolling sum with a floor in Athena/Presto

In Athena, I want to calculate a rolling sum, over a window, that can't go below 0. For example, if summing up a column that has values (1, 2, -1, -2, -1, -2, 1, 2) I should get (1, 3, 2, 0, 0, 0, 1, ...
user avatar
-3 votes
1 answer
41 views

How to write select statment that get data based on partid from last date to current date with filling gapes?

I work on sql server 2012 i face issue i can't make select statment get Partid from last month until current month march (3) . based on last date exist per partid and on same time if there are any ...
user avatar
1 vote
2 answers
49 views

Slow query for multiple data at same timestamp

Hi everyone, I'm currently facing a problem to query efficiently my database to retrieve all rows at same timestamp. I will explain, I have two table of data using TimescaleDb Hypertable with ...
user avatar
3 votes
3 answers
133 views

Use values from one column as column names and sum data from another table

I'm going to do my best to describe this. So I have 3 tables as follows: project id title department_id 1001 Project One 50 1002 Project two 50 metric id name 101 Number of exhibits 102 Number ...
user avatar
2 votes
2 answers
43 views

when make select to top 5 of rows from table it take too much time?

I work on sql server 2019 i face issue when make select top 5 rows it take too much time . Number of Rows on Table Z2DataCore.parts.SourcingNotMappedParts 70 milion rows . when run statment select ...
user avatar
-1 votes
1 answer
29 views

How to get partnumber that have at least have one source type Website and at least one stockid equal 1 Based on PartNumber?

I work on sql server 2017 i face issue i can't get partnumber that have at least source type website and at least have stockid equal 1 per partnumber so with another meaning i need to get part numers ...
user avatar
2 votes
1 answer
30 views

How to optimize simple table with groups and timestamps (SUM over 15M rows with GROUP BY)

I think I have quite simple question, but I couldn't find the answer anywhere. I'm using MySQL and I have a simple table: id timestamp groupId costA costB costC ... 1 2022-02-01 19:45 1 5,13 3,20 30,...
user avatar
  • 21
1 vote
1 answer
39 views

CTE clause not able to be used in final ORDER BY statement

Trying to order by distance using an input geometry t(x), while comparing to geometries from a JOINed table. WITH "nearest_groups" as ( SELECT groups.id, ST_Distance(t.x, locations....
user avatar
0 votes
1 answer
41 views

Postgresql: partition by number of rows after the current one

I need to calculate correlation for each say 3 consecutive rows. For example, assume there is a table with the following data. create table a ( asset_id int, time int not null, value ...
user avatar
  • 395
0 votes
1 answer
40 views

Is there any way to let Postgres know it can filter results and then calculate aggregations?

Given a table A with 10 million records and another table B with child items (one-to-many), I have to provide a consumable view with totals from several fields in B grouped by the FK of A. None of the ...
user avatar
  • 1
1 vote
0 answers
41 views

Aggregate the latest values from a statistics table into a single row by team_id

I've read a whole bunch of StackOverflow questions around this, but I can't seem to crack it so at risk of asking a duplicate question I'm asking my specific one here. I've simplified the question ...
user avatar
1 vote
1 answer
166 views

How to count number of occurrences of each key in a MongoDB collection efficiently (one pass?)?

I'm a MongoDB noob and I have a MongoDB collection with millions of documents, each with tens of keys (average ~ 60, large variance) from a set of around 100. I'd like to get a count of each key ...
user avatar
0 votes
3 answers
64 views

Self-refercing condition in aggregate function

I have a real usecase from a fish farm where the growth of a farm from being fed depends on the average size of the fish in the farm when the fish are fed. I have reduced this problem to what I ...
user avatar
  • 133
2 votes
2 answers
169 views

How to find the rows that have exactly matching relationships in another table

SQL beginner here, so bear with me please. Let's say I have a simplified table songs looking like this id title 1 Villain 2 More 3 More (Laszlo Remix) I also have a table songartists: song_id ...
user avatar
  • 175
1 vote
2 answers
231 views

MySQL - Update column based on multiple conditions

I have a table which has Rows like these: +----------+--------------+---------------------+------+ | CASE_ID | CAT | BIRTH | TYPE | +----------+--------------+------------------...
user avatar
0 votes
0 answers
45 views

Reimplement wm_concat as a user defined aggregate

We have an application running on Oracle 11 that is using wm_concat. We want to migrate it to Oracle 19. As wm_concat is no longer provided in Oracle 19, we considered rewriting all queries using it, ...
user avatar
0 votes
2 answers
27 views

Count rows per set (group of values)

I use PostgreSQL. Given sets of groups such as: CREATE TABLE IF NOT EXISTS t ( id INTEGER NOT NULL, value TEXT NOT NULL ); INSERT INTO t (id, value) VALUES (1, 'A'), (1, 'B'), (2, 'B'), (2, 'A'...
user avatar
0 votes
1 answer
68 views

Group by id and get avg time delta between grouped rows

I have a table with ids and timestamps: postgres=> select id_user, date_seconds from orders.orders order by id_user limit 10; id_user | date_seconds ------------------------...
user avatar
0 votes
0 answers
34 views

HQL aggregate based on other column

Currently working with Spark data frames using HQL syntax (which slightly differs from SQL). I'm trying to aggregate a table by id and want to select the feature row by the most recent date (they may ...
user avatar
  • 101
0 votes
0 answers
125 views

GroupBy with count array in MongoDB

I have a collection and object where this object has a list of objects as following: { "_id": { "$oid": "615617f5b0cd1765b7ba21c1" }, "response": { &...
user avatar
0 votes
2 answers
49 views

Understanding SQL aggregation efficiency

How would the first query compete in terms of performance against the second alternative: Query 1: select count(*) page_views, count(distinct(session_id)) sessions, (count(*) / count(distinct(...
user avatar
0 votes
3 answers
514 views

Difference between TOP and MAX()/MIN() in SQL

In my experience SELECT TOP is much faster than SELECT MIN()/MAX() if you have thousands/millions of rows in your database as seen in this answer and what I've answered here and the askers response. ...
user avatar
  • 135
0 votes
1 answer
47 views

Concatenate duplicates within table column [duplicate]

I have a query that is returning data similar to the following: po_line.line_no (PO Line #) oe_line_po.order_number (Sales order #) 1 A 1 B 1 C 2 D 3 E 3 F 4 null Some of our po_line.line_no ...
user avatar
0 votes
0 answers
97 views

aggregation with group by, inner join and nested conditions in MongoDB

First of all I am sorry if this is a basic question, I am new to queries in MongoDB. Well, what I need is to find the latest registers for a worker in my WorkerLocationContext document and the latest ...
user avatar
1 vote
2 answers
61 views

Converting results to array

I have many entities like: user id | name ----------- 1 | Joe 2 | David 3 | Jane cars id| name ------------ 1 | cars1 2 | cars2 3 | cars3 4 | cars4 5 | cars5 6 | cars6 7 | cars7 8 | cars8 ...
user avatar
  • 15
0 votes
0 answers
17 views

Sybase SUM aggregation bug

we have sybase 15.7 ASE and it works super weird: create table test_data (x float, z float); insert into test_data(x,z) select sum(.1), sum(0.01) ; select * from test_data; and we expect to see "...
user avatar
1 vote
1 answer
172 views

MongoDB - Slow aggregate query

It takes around 6min to get the result from MongoDB, when I use the following aggregate query. db.barcodes.aggregate([ { $lookup: { from: 'company', localField: '...
user avatar
  • 11
0 votes
1 answer
100 views

SQLite Aggregation then Transpose

I am currently uploading my solar inverter data to PVOutput using a Linux machine and a scratch built Perl script. I want to add the meter data from my enrgy supplier. I have an 87,000 row CSV file ...
user avatar
-4 votes
1 answer
72 views

I want to perform max and sum aggregate on same column for different where clause in SQL server [closed]

I have a patient with 2 rows that have same admit and discharge dates and their admit flag is 1 and los is 14, 15 and other patient with 3 rows with different admit and discharge dates that have admit ...
user avatar
0 votes
0 answers
21 views

Union based on string of identifiers Postgresql

I asked this question on the GIS Stackexchange first, but it seems the problem is strictly database related, geometry or not. I assume it would work the same if instead of joining geometry (ST_Union), ...
user avatar
1 vote
1 answer
296 views

ERROR: subquery uses ungrouped column "shops.id" from outer query

I'm encountering an error for the below query: ERROR: subquery uses ungrouped column "shops.id" from outer query LINE 8: WHERE target_id = shops.id AND type = 'started_d... My ...
user avatar
  • 13
0 votes
0 answers
42 views

How to convert array objects with object ids as one of key value pair to string?

I have the following JSON, How to convert an array of objects with object ids to string in MongoDB using aggregation query? { "_id": ObjectId("60e41eb7335412757cc6cb9a"), "...
user avatar
0 votes
1 answer
792 views

How can i convert all object Ids to string using query in MongoDB, note the structure is complex, So can't use $toString in $addFields and $project?

I have a really deep-nested JSON object generated by aggregation query, but I want to convert all the object ids to string format. Tried $toString in $addFields and $project. Since the object is ...
user avatar
3 votes
1 answer
681 views

How can I match the items in an array column, with a different table in Postgres?

I have a table in a Postgres 12 database with that has an array column with a bunch of dog breed guids. I want to lookup these values and basically return the lookup values instead of the guids. ...
user avatar
2 votes
2 answers
167 views

How to compact/group by the results of a query by columns' values?

I have the following table (MySQL version >= 5.7.X, using DBeaver 21.1.0 to manage the db): n_brand population actions_completed_by_unique_user pepsico pepsicoeur 1 pepsico pepsicoeur 1 pepsico ...
user avatar
  • 123
0 votes
1 answer
107 views

Counting MAX event simultaniusly (based in datatime2) within a tallytable

Hi, I have a task that is meant to summarize several connections coming in to a given set of ports (trunks), into timeslots and count the results. The thing is that I would like to achieve two type of ...
user avatar
0 votes
1 answer
559 views

MongoDB Aggregate Poor Index Usage

I've been trying to understand the MongoDB Aggregate process so I can better optimize my queries and I'm confused by usage and $match and $sort together. Sample DB has only one collection people [{ ...
user avatar
0 votes
1 answer
39 views

Aggregating rows from a condition in a linking table in SQL

I am having trouble wrapping my head around a query that gives me these results I have a table that gives me Devices uses per day. dbo.DailyUses Uses | Date | Device 3 | 2020-3-1 | A2 5 |...
user avatar
0 votes
1 answer
31 views

Convert query for count and updated date

I currently run the below query in Snowflake for some of my reconciliations and have never worked in PostgreSQL, which we just adopted. Select count(1), min (LAST_UPDATED_DATE), max(LAST_UPDATED_DATE) ...
user avatar
0 votes
0 answers
44 views

SQL YTD calculations adding periods without postings

I have a query which give me the YTD figures per company (client), account, IC and period for the periods in which financial postings have been performed. The table where I get this from is AAGCONSO ...
user avatar

1
2 3 4 5
12