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.
629
questions
0
votes
1
answer
29
views
How to Sum and Update a Value by Key in PostgreSQL Without Causing Table Bloat?
I'm working with a PostgreSQL database where I need to store a numerical value associated with a specific key.
Over time, I will be continuously adding to this value based on the key.
I want to ensure ...
- 113
0
votes
0
answers
21
views
Best way to aggregate data while applying filters
In many RESTful services that I have built, I often find the need to return a resource with nested resources while applying filters. These are typically retrieved in JSON format.
I have created a ...
- 9
0
votes
1
answer
33
views
Index use after unwind in aggregate
We have a collection in our MongoDB where due to a changed use-case a so-far small query is now really slow. The aggregate in question is somewhat like this:
db.collection.aggregate([
{
$match: {...
- 5
0
votes
1
answer
72
views
PostgreSQL: Approach for aggregating records and upsert vs delete and insert
We have a file processing service that extracts some device configuration information from daily files and stores it into PostgreSQL tables.
We have two layers of tables,
The loading tables that ...
- 143
1
vote
1
answer
76
views
Getting sum for each day based on most recent timestamp entry for each entity
I have a table that contains an ID, timestamp, and integer. Each row is a "snapshot" of the entity at a given time:
TableA
ID
timestamp
num
A
2024-02-04
5
B
2024-02-04
3
C
2024-02-07
8
...
- 177
0
votes
1
answer
72
views
Calculating min/max values of a columnstore table with sys.column_store_segments
I need to fetch the max and min values of an arbitrary column on a table. The table has a clustered columnstore-index, billions of rows, and hundreds of columns.
I'd like to leverage that fact that ...
- 15
4
votes
1
answer
143
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 ...
- 167
0
votes
2
answers
153
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 ...
- 813
1
vote
0
answers
67
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 ...
- 19
0
votes
0
answers
50
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 ...
1
vote
1
answer
47
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 ...
- 111
3
votes
2
answers
355
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 ...
- 151
1
vote
2
answers
157
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.
...
- 13
2
votes
1
answer
474
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, ...
- 23
1
vote
1
answer
148
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 * ...
- 143
1
vote
0
answers
64
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 ...
- 143
0
votes
1
answer
101
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,
...
- 137
2
votes
1
answer
72
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) ...
- 123
0
votes
2
answers
1k
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 ...
- 13
1
vote
1
answer
140
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 ...
- 66
2
votes
2
answers
1k
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 ...
- 123
2
votes
3
answers
69
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
...
- 75
2
votes
2
answers
994
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 ...
- 661
0
votes
2
answers
59
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 ...
0
votes
0
answers
94
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 ...
4
votes
1
answer
2k
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
...
- 41
-1
votes
2
answers
132
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 ...
- 101
0
votes
1
answer
758
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() ...
- 101
0
votes
1
answer
293
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 ...
- 159
0
votes
1
answer
174
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 ...
0
votes
2
answers
36
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
...
- 103
0
votes
1
answer
33
views
Why my rows getting duplicated and showing summing incorrectly?
I have these tables:
Payments
vendor | VendorLocation | VendorName | CheckDate | Amount | TransactionID | InvoiceID
---------------------------------------------------------------------
777 |...
- 125
1
vote
1
answer
93
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 ...
- 25
4
votes
2
answers
3k
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 ...
- 1,497
1
vote
2
answers
1k
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. ...
- 165
0
votes
1
answer
1k
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,
...
- 103
-1
votes
1
answer
115
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. ...
- 99
1
vote
0
answers
25
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 ...
- 111
1
vote
1
answer
251
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,
...
- 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-...
- 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 ...
- 23
0
votes
1
answer
108
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 ...
- 137
1
vote
0
answers
67
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 ...
- 11
5
votes
2
answers
292
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 ...
- 175
0
votes
1
answer
75
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 ...
- 245
7
votes
3
answers
10k
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(...
- 181
0
votes
1
answer
102
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 ...
- 1
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 ...
- 101
2
votes
1
answer
822
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 ...
- 31
0
votes
2
answers
1k
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 ...
- 21