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
4 votes
1 answer
100 views

SQL Server SUM in subquery causing arithmetic overflow when the SUM is less than a 32-bit int

I have a database that has Manifest's which contain multiple Inventory items. Each inventory item has a integer NetWeight. I have the following query: SELECT ID, (SELECT SUM(NetWeight) FROM ...
MrZander's user avatar
  • 167
0 votes
2 answers
75 views

Finalize GroupAggregate parent using Partial HashAggregate child nodes in Postgres query plan

I have seen in some query plans that the parent is a Finalize GroupAggregate but its child nodes are Partial HashAggregates. When does this make sense? As an example, I have a query that resembles ...
ahron's user avatar
  • 803
1 vote
0 answers
66 views

SQL Server not using index in aggregates [closed]

We have a large table called 'TransactionTable' with around 20 million records and there's a index cover all columns used in below query. We also have a temporary table called '#TempTable' with around ...
Blangero's user avatar
0 votes
0 answers
32 views

SQL Query group by to find EffectiveFrom and EffectiveTo Dates for repeating key value pair

I have a table with a snapshot of repeated data for a key and value pair with record_timestamps. The dataset can also contain late-arriving historical record_timestamps as well. I want to generate an ...
user137279's user avatar
1 vote
1 answer
35 views

How to "move" data from rows to columns with aggregate data?

I count the number of occurrences by the event type, so this is the SQL query: select date(created), event_type, count(*) from public.event group by date(created), event_name Is it ...
mimic's user avatar
  • 111
3 votes
2 answers
132 views

Managing the order of aggregation in Postgres

Suppose I have the following merging function that merges two jsonb values overwriting duplicate keys with the values from the second create or replace function jsonb_concat(a jsonb, b jsonb) returns ...
Dragas's user avatar
  • 151
1 vote
2 answers
29 views

Comparing an aggregate function's result against a column

i have 2 tables like these; invoices id | total_amount | currency | discount payments id | invoice | amount As you can guess, payments table keeps records of the payment made to a specific invoice. ...
cem ekkazan's user avatar
2 votes
1 answer
135 views

When to use an array aggregate vs a lateral join array subquery in postgres

tl;dr: When to use array_agg over a lateral join with array subquery? Context: create table t_zipcode ( zipcode text primary key, location geography ); create table t_user ( id text primary key, ...
ed__'s user avatar
  • 23
1 vote
1 answer
84 views

Aggregate SCD Type 2 data "as of" each day

Problem When working with SCD type 2 data it's easy to see the state of a table "as of" a given point in time by using your date columns, eg: valid_from and valid_to. For example: select * ...
camtech's user avatar
  • 133
1 vote
0 answers
55 views

Continious aggregates (keeping aggregated data in a fresh state) for PostgreSQL

I build a PostgreSQL database for movie db platform. This DB is heavy loaded for readings of many related data. Here is simplified schema what I have: create table items ( id ...
Dmitry K.'s user avatar
  • 143
0 votes
1 answer
70 views

User-defined aggregate function to sum jsonb values

I have agg function that sums decimals. Business logic omitted for brevity CREATE OR REPLACE FUNCTION calculate_snapshot_internal_decimal(_this_value DECIMAL, ...
Capacytron's user avatar
2 votes
1 answer
54 views

How to use aggregate column in where clause when using group by

I've following table CREATE TABLE `books` ( `book_id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(100) DEFAULT NULL, `author_fname` varchar(100) DEFAULT NULL, `author_lname` varchar(100) ...
Md. A. Apu's user avatar
0 votes
2 answers
511 views

Is it a good idea to join data from two different databases?

I am building an application that requires a lot of tables in one database and while the joining and aggregation of data is really nice and seamless, I am starting to wonder if I am creating too many ...
jaffer_syed's user avatar
1 vote
1 answer
92 views

Rolling up multiple varbinary(max) rows into a single row

My table looks something like this: BinFiles 0x1101ABB1C... 0x2202DEE2F... 0x3303GHH3I... I tried this to get a single line: SELECT STUFF((SELECT d.BinFiles FROM dbo.demo d WHERE d.ID ...
mbielsa's user avatar
  • 56
2 votes
2 answers
614 views

Ignore duplicate rows in a join - and take only one of them

In MySQL I have tables, simplified as follows: companies (id, name, rank, trade_sector_id) orders (id, company_id, paid, subtotal) trade_sectors (id, label) companies_notes (id, company_id, notes) I ...
Ade's user avatar
  • 123
2 votes
3 answers
61 views

Find percentage based on values for each ID

I have table1 with 2 ID's and 2 values per ID (Y,N). I can count the values by the following query: select id ,count(*) as "total" ,choice from table1 where id in (1,8) group by id, choice ...
tkmagnet's user avatar
0 votes
0 answers
21 views

Find document with regular expressions in collections related to populate in mongoose

The aim is to filter a collection of documents (Requests) based on the document field from another collection (Patients, Clients, Doctors, and Services), of which there are several references in the ...
Izlia's user avatar
  • 101
0 votes
0 answers
25 views

MongoDB: Complicate aggregation operation speed optimization

We want to calculate the average weight reduced and total weight loss users according to scales collection. The following steps describe our aggregation: Firstly, we calculate the average weight for ...
Hhry's user avatar
  • 111
2 votes
2 answers
752 views

Does PERCENTILE_CONT still give terrible performance when calculating medians in 2022 versions of SQL Server?

I'm currently reading a book from SQL Server 2014. It, like every other online source that I've found, tells you that PERCENTILE_CONT is a very slow way of calculating medians and does not show the ...
J. Mini's user avatar
  • 121
0 votes
0 answers
37 views

Storing Aggregation pipeline inside materialized view in Mongo

I am quite new to Mongo Coming from Sql background. I have a aggregation on collection_1 which involves $graphlookup (given below) [ { $match: { $or: [ { objStatus: "...
Arun's user avatar
  • 13
0 votes
2 answers
48 views

How I do I aggregate or perform calculations on a time datatype in "MM:SS:MS" in MySQL?

I'm currently working on an NBA project and I need to take the average minutes played per player, but I have no idea how to aggregate or calculate the time datatype. I know you have to convert it, but ...
thisisforfun's user avatar
0 votes
0 answers
88 views

Counting Rows From a Value in a SQL text box - please please can anyone help?

I just can't find an example of this anywhere and hoping someone can help. I have a query which returns a list of care services and other fields based on the care service table values and calculated ...
Paula Morrison's user avatar
2 votes
1 answer
1k views

Create Postgres hash aggregate function

Postgres: 15.1 My goal is simple: I want to create an aggregated hash value for x rows from column c in table z. Example Input: SELECT hash_agg(c) AS checksum FROM z; Example Output: checksum ...
Yuki's user avatar
  • 21
-1 votes
2 answers
83 views

What properties should the column in GROUP BY Clause have in SQL?

I've found group by to be very confusing topic in SQL. The database vendor doesn't matter to me but I'm using MySQL. Till now, I've done lots of reading, and I'm still unable to determine what the ...
zeeshanseikh's user avatar
0 votes
1 answer
513 views

Oracle equivalent of PostgreSQL ARRAY_AGG

I'm looking for an aggregate function supported by Oracle SQL to collect values into arrays, something similar to PostgreSQL ARRAY_AGG. I already use listagg for strings, I use cast() with collect() ...
gouessej's user avatar
  • 101
0 votes
1 answer
242 views

How does MySQL handles GROUP BY?

I'm on Windows 10, MySQL Workbench. I'm on MySQL 8.0. Here's the dataset. northwind.sql. https://pastebin.com/bMgjXvfT Objective: Write a query to get the order ID, customer's name, grand total of ...
achhainsan's user avatar
0 votes
1 answer
141 views

Calculate average for multiple subsets simultaneously

I have a MySQL database (for testing: through WAMP) with among other things a collection of temperatures for a bunch of likewise devices. Now I want to calculate the average temperature for each ...
StevenCellist's user avatar
0 votes
0 answers
21 views

Query that accesses 2 feilds of document in collection

I have a users collection of documents containing fields city_name, and income. How can I write a query which gives me the documents data of the top 10 cities which have the highest number of users ...
Frecoder's user avatar
0 votes
2 answers
33 views

SQL: Counting aggregates?

I'm not quite sure how to phrase this question, which is part of the reason I am having problems producing or finding an answer. Given the following table: id class region_id rank ...
Lee Goddard's user avatar
0 votes
1 answer
31 views

Why my rows getting duplicated and showing summing incorrectly?

I have these tables: Payments vendor | VendorLocation | VendorName | CheckDate | Amount | TransactionID | InvoiceID --------------------------------------------------------------------- 777 |...
sql-noob's user avatar
  • 125
1 vote
1 answer
38 views

Min over time using mysql

I can generate the data for the left four columns of this data set using mysql - however i want the database to generate the column on the right for (I did this manually as an exmaple). The column on ...
pman's user avatar
  • 25
4 votes
2 answers
2k views

PostgreSQL equivalent to Oracle's ANY_VALUE(...) KEEP (DENSE_RANK FIRST/LAST ORDER BY ...)

There's a technique in Oracle SQL that can be used to simplify aggregation queries: Aggregate on a particular column, but get information from a different column, using a simple calculated column in ...
User1974's user avatar
  • 1,457
1 vote
2 answers
893 views

How to use LIMIT and OFFSET in Postgres' json_agg() function

With reference to this question and this SQL Fiddle, I currently have a working SQL query, based off of this answer, that gets a tag from the database and aggregates all articles having such a tag. ...
Sirjon's user avatar
  • 165
0 votes
1 answer
924 views

Reference ARRAY_AGG - length in WHERE clause

My question is similar to possible reference jsonb_array_length in a where clause, however I cannot use ORDER BY. I have following tables: CREATE TABLE IF NOT EXISTS games ( id SERIAL PRIMARY KEY, ...
Alexey Vol's user avatar
-1 votes
1 answer
97 views

Real Time Analytics: Which database?

We are currently using MongoDB, and it is performing well for our needs. However, we are looking to support better real-time analytics and aggregations, which MongoDB doesn't handle effectively. ...
Nedim's user avatar
  • 99
1 vote
0 answers
22 views

query customer retention over range

I am trying to find the best way to accomplish the following. Get the beginning customer count, which carries from the previous day Get New Customer count Get the number of Customers who have not ...
thedemon's user avatar
  • 111
1 vote
1 answer
212 views

Returning array of JSON objects from related fields in PostgreSQL

I have the following schema and some data inserted into the DB. Schema (PostgreSQL v14) CREATE TABLE users( id INT NOT NULL PRIMARY KEY, email TEXT NOT NULL UNIQUE, password TEXT NOT NULL, ...
Sirjon's user avatar
  • 165
3 votes
2 answers
2k views

How can I compute the TOTAL of multiple COLUMNS from a PIVOT?

I have the following ORACLE SQL table/data: A DATE_FIELD C D 1 01-JAN-13 hello 0 2 01-FEB-13 wonderful 10 3 20-MAR-13 world 20 4 21-APR-13 tree 10 5 21-JUN-13 tree 30 6 01-JUL-13 world 10 7 30-...
xentwo's user avatar
  • 33
2 votes
1 answer
25 views

Every value in Column 1 Needs every other value in Column 1 as a new row

I am stuck on some code. As shown in the image, I need each state to be given a new row for every other road project in the country. Ohio, for example, is given 2 new rows in the new table, 1 for each ...
Illy's user avatar
  • 23
0 votes
1 answer
95 views

Time series analysis and custom aggregate function for Postgres

I have a table with time series. It contains snapshot and increment value snapshot means concrete value increment means take previous snapshot and add increment value to get snapshot value sensor_id ...
Capacytron's user avatar
1 vote
0 answers
59 views

Group by Rollup not rolling up - Except in 1 case

I want to perform a group by rollup calculation (I know varchar as ID is bad - it's out of my control) The query is correct insofar as each individual department but the sub-departments are not being ...
N0ug4t's user avatar
  • 11
5 votes
2 answers
291 views

Simultaneous aggregate count and full count

I have a table events like this: create table events ( correlation_id char(26) not null, user_id bigint, task_id bigint not null, location_id bigint, type ...
Moshe Katz's user avatar
0 votes
1 answer
71 views

Sort and Select by distinct field when using an aggregate function

I want to select all clicks and views for certain objects. Clicks and views are tracked in separate tables, but I want 1 row returned for an object with the views and clicks combined. This works ...
Adam's user avatar
  • 245
7 votes
3 answers
9k views

SQLite - How does Count work without GROUP BY?

With a table like: first_name last_name ------------------------ jack frost john wayne betty white null jackson And this query: select first_name, COUNT(...
John's user avatar
  • 181
0 votes
1 answer
101 views

MYSQL - return the minimum number of records so price sum is possible to be as close to the input price

I have a table with names, prices and a user input price, for example, 100. I want to return a minimum number of records so prices sum will be close to the input price So If I have in records items ...
lekinio's user avatar
0 votes
1 answer
2k views

Return type mismatch in function declared

I am working in supabase, where I want to make a function with the help of SQL queries to obtain the last record of a user. As it is a query that would not look entirely good using the traditional ...
Izlia's user avatar
  • 101
2 votes
1 answer
684 views

Do multiple MYSQL json_arrayagg(s) preserve order?

select media_id, json_arrayagg(artist_id) as artist_ids, json_arrayagg(role) as role_ids from x_media__artist xma group by media_id Hi guys, do artist_ids and role_ids preserve their order as ...
Joe Yan's user avatar
  • 31
0 votes
2 answers
964 views

How to count rows matching certain conditions within each group?

I have more columns in my table vb10_cartridgedata but the following two columns are my columns of interest: NestID and FailCode. NestID can only contain numbers from 1 to 16. I am trying to count ...
Lalo Quera's user avatar
0 votes
1 answer
358 views

In Postgres, what is the best manner of aggregating the values of a "returning" clause

I would like to query an aggregate of values that get updated. For a simple and contrived example, if this were legal syntax I would do : update mytable set mynumber = mynumber + 1 returning sum(...
user2565741's user avatar
0 votes
1 answer
47 views

A discussion about optimal DB design for saving aggregations

I have raw data. There are 4 filters defined on this raw data: f1...f4. There are 50k~ permutations of f1...f4 filters. There are 2000~ time windows. Each time window is 1 hour. For each time window, ...
igal leikin's user avatar

1
2 3 4 5
13