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.
627
questions
-2
votes
1
answer
246
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 ...
6
votes
1
answer
12k
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 ...
-1
votes
3
answers
541
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
0
answers
20
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
1
answer
472
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
1
answer
1k
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
2
answers
244
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
1
answer
316
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
0
answers
593
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
1
answer
877
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
1
answer
723
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,...
7
votes
2
answers
18k
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
2
answers
631
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 ...
1
vote
1
answer
189
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 ...
1
vote
2
answers
324
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
1
answer
1k
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 ...
2
votes
2
answers
406
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
1
answer
90
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 ...
2
votes
2
answers
930
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
1
answer
2k
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
),
...
2
votes
1
answer
71
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
0
answers
67
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
0
answers
55
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
1
answer
39
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:...
6
votes
3
answers
782
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
vote
1
answer
830
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
1
answer
145
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:
┌───────┬───────────────────────┬───────┬───────...
8
votes
1
answer
5k
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 ...
1
vote
2
answers
687
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
1
answer
4k
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
1
answer
581
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
1
answer
54
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
2
answers
583
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
1
answer
619
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 ...
-1
votes
2
answers
7k
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
2
answers
108
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
1
answer
570
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
1
answer
3k
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
1
answer
1k
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 ...
3
votes
1
answer
1k
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
1
answer
50
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
1
answer
514
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
1
answer
49
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
2
answers
53
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
2
answers
56
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
0
answers
65
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.
...
2
votes
1
answer
137
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
1
answer
68
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
1
answer
199
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
1
answer
678
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 ...