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
0
votes
1answer
19 views

total drinks per day column

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
29 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
4answers
915 views

json_object_agg errors on null in field name

PostgreSQL versions: my local install 11.3 and the below fiddle is on 10.0. Both behave the same. I have a schema of pages, each page has sections and each section can have different kind of ...
0
votes
1answer
117 views

Slow performance of query with $and & $or operators

My query with $and, $or operators is performing poorly. Even though I've built a compound index on all three fields that are specified in my query criteria, the query does a complete collection scan. ...
-1
votes
0answers
24 views

Combine multiple rows into single row using Teradata

I want to achieve single row output from multiple rows for below 3 cases using Teradata SQL Assistant. Can someone please help me framing a generic query that can handle all of below 3 cases. Any help ...
-1
votes
3answers
15k views

MySQL query with LIKE operator for multiple values, with aggregate [closed]

How should I write a query using the LIKE operator checking for multiple values. Something similar to LIKE ('car%','bus%') output I want: car 373 Bus 2 Heavy Vehicle 3 Motor ...
0
votes
0answers
17 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
1answer
353 views

Calculate Count Distinct without grouped columns in Oracle

I am trying to extract a few records and I ran into the "not a single group" error. This error can be resolved by removing the (count(distinct dc.DUCT_FID)) as NO_OF_WAYS column expression. However, I ...
0
votes
0answers
5 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, ...
2
votes
2answers
2k views

Time series data - question for daily aggregation query

I'm trying to query a MySQL database with hourly timeseries data but I'm stuck with (daily) aggregation. My raw data: mysql> SELECT * FROM emoncms.custom_registers -> WHERE timestamp >=...
-1
votes
3answers
33 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
1answer
41 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 ...
0
votes
0answers
18 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
155 views

Aggregate sampler function for programmer express predictions

Are there any SQL-standard or DBMS implementation that offers "sample aggregator" for predictably repeated columns? I will explain, see bold text at the end. PS: this question is about "little ...
2
votes
1answer
51 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 ...
2
votes
1answer
1k views

Multiple to_json(array_agg), separate joins

I'm building a messaging system, and trying to get a list of messages and their replies; and also join the participants of the message threads. So I'm shooting for messages = [{id, subject, body, ...
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" ...
4
votes
4answers
8k views

Median with GROUP BY

Suppose the following table t1: ================= | tag | val | --+ for the sake of simplicity, val is non NULL ================= | a1 | v1 | | a1 | v2 | | a1 | v3 | | ...
1
vote
1answer
506 views

Postgresql split date range by business days then aggregate by month

I have the following rows in a table (dd-mm-yyyy). Start | End | Value --------------------------------- 01-01-2019 | 31-03-2019 | 64 01-02-2019 | 30-04-2019 | 126 01-03-2019 | 31-05-2019 ...
0
votes
1answer
13 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
26 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....
3
votes
2answers
81 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
1answer
69 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
32 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,...
0
votes
3answers
1k views

Percent of total: Is there a more efficient way than using a subselect?

I have an existing ASSET_SUMMARY_VW view: +-----------------------+---------------+ | ASSET_TYPE | COST | +-----------------------+---------------+ | TRANSPORTATION | 1,000,...
0
votes
2answers
28 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
229 views

Does bool_and short-circuit function calls with arguments?

Suppose I have the following query: SELECT a.* FROM a CROSS JOIN b GROUP BY a.id -- Primary key HAVING bool_and(expensive_comparison(a, b)) What will PostgreSQL do with this query? Will it process ...
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
33 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, ...
5
votes
3answers
28k views

Sum of previous n number of columns based on some category

I have following input for which I need to calculate the sum of values for previous x number of weeks for each category. If the x is 3 the output would look like this: Note that the last value is 49 ...
6
votes
3answers
9k views

GROUP BY gives wrong result with MIN() aggregate function

I have a table that looks like this: +------+--------+----------+------+--------+-------+------+------------+ | id | code | category | mq | weight | weave | show | min(price) | +------+--------+...
2
votes
1answer
44 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 ...
14
votes
2answers
7k views

Why is array_agg() slower than the non-aggregate ARRAY() constructor?

I was just reviewing some old code written for pre-8.4 PostgreSQL, and I saw something really nifty. I remember having a custom function do some of this back in the day, but I forgot what pre-...
2
votes
1answer
114 views

Postgres - Querying Min, Max and getting associated columns data specific to those result of those rows

I have query in which returns averaged values by site and month. What I want to know is the min and max of those monthly averages by site and (the difficult part) the month when each occurred. Here ...
5
votes
2answers
3k views

How can I pass column to function in sql?

I'm calculating median as: DECLARE @TEMP TABLE ( ID INT ) Select ( ( Select Top 1 ID From ( Select Top 50 Percent ID ...
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
2answers
169 views

sum only different id's values with any aggreagte function in postgresql

I need your help. Let's assume that we have a table like below +---------------+-------+--------+----------+ | barcode | shop | id | entrance | +---------------+-------+--------+----------+...
2
votes
4answers
1k views

Vendors with Most Invoices in State

I have the tables Vendors(VendorName, VendorID,...) and Invoices(InvoiceID,...) and I am trying to find vendors with the most invoices in their respective states. Please critique my work: SELECT Max(...
0
votes
1answer
41 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 ...
0
votes
2answers
257 views

Access Web App query 3 Tables with aggregatios - Need a workaround

I'm designing an Access 2016 Web App for inventoring our computers. The problem I have is with a query where I want to get the CPUCore-amount and RAM-Amount for each PC. The computer-value in RAM ...
1
vote
2answers
46 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
902 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
39 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....
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 ...
2
votes
1answer
44 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
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
527 views

aggregating json fields

I have a table that holds JSON data. In a query with a GROUP BY clause, I'd like to get an array of all of the JSON field names in the result set. I tried a query like this: SELECT array_agg(...
-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
57 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 ...

1
2 3 4 5
10