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
-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 ...
user209047's user avatar
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 ...
tinlyx's user avatar
  • 3,540
-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 ...
nancy_olson's user avatar
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 ...
Larry B's user avatar
  • 43
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 ...
Rob Gravelle's user avatar
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 ...
homer5439's user avatar
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" ...
RedM's user avatar
  • 145
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,...
Xaser's user avatar
  • 115
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....
blizz's user avatar
  • 157
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 ...
Ras's user avatar
  • 3
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,...
oneCoderToRuleThem's user avatar
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 ...
oneCoderToRuleThemAll's user avatar
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 ...
user9645's user avatar
  • 177
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 ...
JOSH W's user avatar
  • 27
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, ...
uzyn's user avatar
  • 111
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 ...
homer5439's user avatar
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 ...
cis's user avatar
  • 449
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 ...
Someguywhocodes's user avatar
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 ...
tinlyx's user avatar
  • 3,540
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 ), ...
J.D.'s user avatar
  • 37.3k
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 ...
AWGIS's user avatar
  • 121
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 ...
StixO's user avatar
  • 167
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,...
arthur's user avatar
  • 11
-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:...
Andrei's user avatar
  • 111
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 ...
Wildcard's user avatar
  • 588
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 ...
slombo's user avatar
  • 13
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: ┌───────┬───────────────────────┬───────┬───────...
Florian Klein's user avatar
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 ...
tinlyx's user avatar
  • 3,540
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 ...
arty's user avatar
  • 115
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: { ...
Ritik Saxena's user avatar
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 ...
Jetals's user avatar
  • 11
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 ...
c24b's user avatar
  • 101
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 ...
TH58PZ700U's user avatar
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 ...
Yasser Alsawy's user avatar
-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 ...
Mango Durango's user avatar
-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), ...
Hisanth's user avatar
  • 13
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 ...
TmTron's user avatar
  • 243
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 (...
edaus's user avatar
  • 21
-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 ...
user6308605's user avatar
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 ...
Anana Aristotle's user avatar
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, ( ...
D3 K's user avatar
  • 101
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,...
Wildcard's user avatar
  • 588
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 ...
vellai durai's user avatar
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 ...
RBB's user avatar
  • 785
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, ...
Sam's user avatar
  • 13
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. ...
user190765's user avatar
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: ...
gillesB's user avatar
  • 165
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 ...
m0rgul's user avatar
  • 23
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 (...
Nebehr Gudahtt's user avatar
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 ...
edo's user avatar
  • 25

1 2 3
4
5
13