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
3
votes
2answers
59 views

How to create index to improve performance of an aggregate function that creates a table in oracle

I am creating an Oracle ORDS API using APEX_JSON. I recently started using bind variables instead of string concatenation using ||. I am trying to use an in clause in my where condition. The problems ...
1
vote
2answers
33 views

Unexpected behaviour for GROUP BY query

CREATE TABLE public.temp ( id integer, a integer, b integer, x integer ); select x, a, b from temp group by id, a, b; ends up with expected error: ERROR: column "temp.x&...
0
votes
1answer
9 views

Is there a better way to summarize a table containing events of different types by identifier?

I have this table definition for sensor events: Table "public.sen_sensor_event" Column | Type | Collation | Nullable | Default ------------...
0
votes
1answer
17 views

Compute duration of an event based on consecutive values in another column

I need to compute the sum of durations of multiple events based on consecutive values in another column. ts | w ----------------------------+--- 2020-07-27 15:40:04.045+00 | ...
1
vote
1answer
38 views

GROUPING SETS query with many-to-many relationships

I'm writing a query that is mostly based on a GROUPING SET. A very simplified version of the query is the following: SELECT GROUPING(a,b) as grouping, a, b , COUNT(*) FROM table GROUP BY GROUPING ...
-1
votes
0answers
11 views

How to get availability of time slots by aggregate query?

I am new in MongoDB and stuck in query. Please help me. I have the two collections name staffMembers and membersTimeSlots whose responses are following: Here is the response of staffMembers collection:...
-1
votes
1answer
25 views

DB2 - Select rows with oldest date per id and aggregate a field with similar date

Here's what I want to do (Please see photo for reference): I wanted to select from Source Table all customer (cust_id) and their corresponding oldest date of payment (pd_date) and aggregate using sum()...
1
vote
1answer
40 views

SQL Server & SSDT : Error modeling database during Publish after adding CREATE AGGREGATE / EXTERNAL NAME

I am using SSDT to create a database containing CLR user defined aggregates. The aggregates are intended to sit in different schema. That is not possible with CLR objects inside SSDT, without using a ...
0
votes
1answer
35 views

Aggregate rows for each group of rows

I am trying to solve a problem which is quite easy to solve in a procedural language, but I fail to solve it in SQL in an efficient manner. Let me first explain the problem. I have a series of events ...
0
votes
1answer
16 views

Add aggregated value filtered by a condition to existing query

I have this table to collect data from IoT device: CREATE TABLE public.conferimenti ( id bigint NOT NULL DEFAULT nextval('conferimenti_id_seq'::regclass), codicestazione smallint NOT NULL, ...
1
vote
1answer
42 views

Using aggregate function on data masked column returns zeroes

Recently we have implemented dynamic data masking in SQL Server 2019 database in order to hide sensitive information from developers. However, for testing purposes I'd like them to see close-to-real ...
0
votes
1answer
9 views

Selecting non-aggregate column on 3 table inner join

I have 3 tables in a standard parent/child kind of "belongs to" relationship in postgres: donations --(belong to)--> campaigns --(belong to)--> organisations Each table has an id ...
-1
votes
3answers
49 views

How to aggregate in the WHERE clause referencing the subquery from the FROM clause

I got the following query so far: SELECT q1.ID, Content, Volume, CustomerID, runtot FROM ( SELECT ID, Content, Volume, CustomerID, SUM(Volume) OVER (PARTITION BY CustomerID ORDER BY ID) AS ...
5
votes
2answers
218 views

Datetime progressive sum PostgreSQL

my first question here, but I looked in many other places and could not find the answer to my problem. id | track_seg_id | time ----+--------------+--------------------- 1 | ...
0
votes
1answer
38 views

Postgres group and aggregate(sum) JSONb arrays and non JSONb properties

Version PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc, a 224fe214a p 3971489d3e, 64-bit I am trying to build a query which (distinctly) groups values from JSONb columns, and also sums values ...
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 ...
0
votes
1answer
39 views

How to get best sellers per headquarters [closed]

Hi i need to get the best seller of all products per headquarters: I have this query: SELECT Employees.first_name, Employees.last_name , Sold_products.product_type, Headquarters.country ,...
0
votes
1answer
21 views

total drinks per day column [closed]

Attempting to select a table with a column where the customers total drinks ordered per day are calculated by the odate and drink number. EG. Claude orders 10 drinks total on the 2020-1-12 drinker | ...
-2
votes
1answer
30 views

Getting average in the where clause using alias subqueries in sqlserver

Hello I'm new at SQL and I've been asked to get the rent duration where rent duration is more than the average rent duration, but I can't seem to do it correctly. What am I doing wrong? This is the ...
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, ...
-1
votes
3answers
34 views

Group by a col and concatenate two non group by col in oracel

I'm getting into SQL for the first time after 9 years of work only into Linux. Just during the course of practising sql i was trying to concatenate first_name and last name and get the avg salary of ...
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
48 views

List top N rentals for each month

I'm trying to work out the SQL to list the top 5 rentals for each month on MySQL or SQL Server. The database is the Sakila Sample database and the table is rental. Here are the first several rows in ...
2
votes
1answer
85 views

Very slow postgresql aggregation

EDIT: docker image for testing with the same data (obfuscated) $ docker run --rm --name pg -d homer5439/pgagg $ docker exec -ti pg bash # createdb -U postgres test; zcat /tmp/corr.sql.gz | psql -U ...
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
1answer
15 views

How to enforce strict handling of GROUP BY with non-aggregate / bare columns or UPDATE with multiple-row SELECT in SQLite

Unlike some other RDBMS, SQLite does allow for bare columns in SELECT querys with GROUP BY, i.e. not all selected columns need to be part of the group by or aggregate functions otherwise. In this case,...
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
1answer
87 views

(mySQL Aggregate function, JOINS, and GROUP BY) sakila database project

The current pandemic is making it very difficult to contact my teachers for assistance, so I'm hoping I can describe my problem well enough to try getting some help here. The database used is the ...
0
votes
1answer
38 views

Understanding the use of bitwise operators in MySQL?

Can someone explain the purpose of using bitwise operators(like BIT_OR) in MySQL queries. For example, if have a table such as following: What is the purpose of aggregate operation like: SELECT name,...
3
votes
2answers
136 views

MySQL: Using DISTINCT and GROUP BY together?

I saw the following MySQL query that that uses both DISTINCT and GROUP BY together: SELECT DISTINCT user_id, post_id, post_content FROM some_table GROUP BY post_id, user_id HAVING ...
0
votes
2answers
33 views

How to sort aggregate function call

If I have an expensive function call that takes a JSONB and returns an INT, e.g. CREATE FUNCTION my_expensive_function(data JSONB) RETURNS INTEGER AS How do I go about ordering the aggregate result ...
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..." }, { ... }] } ...
1
vote
1answer
29 views

Merging Continous Date Ranges with upto 63 days in between

I am trying to write a query that will take multiple date ranges and combine them into a contiguous date range. The catch is that I have to allow for up to 63 days in between the last termination date ...
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, ...
2
votes
1answer
47 views

Merging one-to-many and one-to-one relation in the same JSON object in postgresql

LINK TO FIDDLE: https://www.db-fiddle.com/f/a6FXTqJHpU9smuJTUKqwuV/0 My tables are as follows: => \d ops; Table "public.ops" Column ...
1
vote
2answers
38 views

WHERE clause not preventing return of row but changing values for aggregate function (here: count)

I have recently discovered a PostgreSQL behavior which is strange and problematic in my opinion. Consider this simple query SELECT 'something confidential' WHERE FALSE; The nonsense clause ...
0
votes
1answer
46 views

Compare running / cumulative total with static target

I have some daily data and monthly data. I want to compare these two datasets, with Hive and perform a payout when the sum of actual for a month passes that respective months target. In the above ...
1
vote
2answers
52 views

Inconsistent behavior of aggregate functions about whether to return NULL on empty input?

On an empty input table, PostgreSQL (12.x) is returning NULL on some aggregate functions like array_agg() but normal values for others like count(), as shown below: => SELECT count(*) IS NULL FROM ...
5
votes
1answer
903 views

Why am I getting nondeterministic results with the deterministic function STDEV()?

This is type of query I'm trying to run: WITH CTE_Ordered AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY PartitionField ORDER BY DateField) AS PartitionRowId FROM SourceTable ), ...
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....
2
votes
1answer
45 views

Create new table with a sum where row from table 2 matches field in table 1 and aggregate on another field

I am fairly new to SQL so apologies if my terminology is incorrect. I am looking to summarise a number of measurable's from "Table 2" but in the example below I have only included 1 for ease. I would ...
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,...
-2
votes
1answer
28 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
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 = ...
5
votes
3answers
45 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 ...

1
2 3 4 5
11