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
0answers
6 views

select rows and calculate number of rows based on parent column value

I have a total of three tables with a many-to-many relationship. 1.) users id | email ----------- 1 | usera@example.com 2 | userb@example.com 3 | userc@example.com 2.) accounts id | userId | ...
0
votes
0answers
27 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. ...
1
vote
1answer
19 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
1answer
28 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
0answers
6 views

How to get records from one collection based on role hierarchy assigned from another collection in mongodb using $graphLookup aggregation

I have a roles collection whose schema is like this : { name:String, parent:{type:Schema.Types.ObjectId, ref:"role"} } it has 3 documents right now as shown below { "_id": "...
0
votes
1answer
19 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
1answer
67 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 ...
2
votes
0answers
11 views

Relational Algebra: how to express the combination of aggregation and projection?

Here I have a table, I would like to query the average salary of departments, the SQL statement should be select dept_name, avg(salary) from employee I try to express this SQL in relational algebra....
0
votes
1answer
17 views

How to rank an aggregation in each newly formed group?

The problem below that I would like to solve sounds quite simple but it is quite difficult for me to arrive at the solution with SQL. Let's say I have a simplified ERD that looks as follows: This ...
1
vote
3answers
54 views

Take average of multiple nullable columns

I have an example table (year) as below in PostgreSQL 9.5: Name | 2010 | 2011 | 2012 ------------------------- A | 10 | | 40 B | 10 | 20 | 30 Now, if I write a simple query as ...
0
votes
2answers
51 views

join two collections with group by and sum in Mongodb

i have this two collection and i want to join, after this I want to sum the value of TotalPrice each orderdate : some line of my two collections the first collection date : {"Id_Date":1,"...
0
votes
0answers
28 views

User-defined harmonic mean function performs worse than query in PostgreSQL 9.6

We created a harmonic mean function based on this blog post: CREATE OR REPLACE FUNCTION public.harmean_accum(numeric[], numeric) RETURNS numeric[] LANGUAGE sql STABLE COST 1 AS $h$ SELECT array[$1[1]+...
0
votes
3answers
84 views

SELECT minimum value without a subquery

I have this table: auctionid bid bidtime bidder bidderrate openbid price 8211851222 55 6.021053 smoothy43m 69 50 161 8211851222 51 6.378843 ahmadf316 ...
0
votes
1answer
23 views

How to aggregate results from SELECT JOIN

I have two tables: **user Table ** id username --- ----------- 100 greg 101 john 102 patrick 103 bill **user_subscription_plan Table ** id user_id amount --- --------- ------- 1 ...
10
votes
1answer
102 views

Hash aggregate bailout

A question that arose in a chat discussion: I know hash join bailout switches internally to a sort of nested loops thing. What does SQL Server do for a hash aggregate bailout (if it can happen at ...
1
vote
0answers
51 views

Group by equidistant integer intervals

I am using TimescaleDB (an extension to PostgreSQL) to store altitude data from a GPS sensor. The table looks like: CREATE TABLE IF NOT EXISTS altitude ( time TIMESTAMPTZ NOT ...
0
votes
0answers
15 views

How to query Groupy by and count with lookup in Mongodb?

I am trying To use group by in a collection and COUNT in another collection. I have 2 collections. One for customer and other for order_fact. Example Sample data for collection customer. {"Id_Cust":...
0
votes
1answer
18 views

three tables relation order is correct but values are false

i have those tables table1 | id | name | | 1 | axe | | 2 | bow | | 3 | car | | 4 | dart | table2 table3 | t1_id | ...
1
vote
3answers
57 views

Is there a way to get count based on multiple columns but still have it be distinct in SQL?

I have included two pictures that are in Excel just for ease of viewing but I am doing the queries in SQL. Hopefully, this is possible as the algorithm I have is not giving me the correct results.
1
vote
2answers
31 views

Multiple aggregates with cross (lateral) join

Following on from Combining array_agg and unnest, given this data: key | a | b | c ------------------ 1 | 0 | 1 | {1,2} 1 | 1 | 2 | {3} 1 | -1 | 3 | {2} 1 | 2 | 4 | {} Executing this ...
0
votes
1answer
41 views

What is the difference between these invocations of COUNT()? [duplicate]

can anyone please let me know difference between count(*), count(1), count(0), count('FAST')
2
votes
1answer
159 views

Combining array_agg and unnest

Given a dataset (with a GIN index on values): key | values ------------- 1 | {4,2,1} 1 | {2,5} 2 | {4,1,3} I want to aggregate the arrays: key | values ------------- 1 | {4,2,1,5} 2 | {4,...
0
votes
1answer
79 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 ...
11
votes
1answer
829 views

Can we put an equal sign (=) after aggregate functions in Transact-SQL?

I have encountered a script like this: set @sum = sum = (case when somecol1 is null then DATEDIFF(d,[somecol2],somecol3) else 0 end) I can't understand the meaning of the equal sign (=) after the ...
0
votes
0answers
15 views

Postgres count the same join by different values

I've been trying to create a query that counts the number of different types of events associated with each entry in another table city job job_events id | name | state ...
0
votes
1answer
41 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. ...
0
votes
3answers
51 views

Usage of aggregate function with when self-joining a table

I want to find people from a database who has a weight bigger then the average weight: SELECT * FROM Player p1, Player p2 WHERE p1.weight > avg(p2.weight) But I have an error: Result: misuse of ...
0
votes
1answer
28 views

Two aggregated sub queries wont run

I am having trouble with the below, I need to average two columns in a correlated subquery and I cannot get it to work, how do i get both aggregate sub queries to run then go through the outer query? ...
0
votes
1answer
18 views

How to summate weekly totals from a postgres date range type per user

I have the following table: Schedules +----+---------+------------+-------+--------------------------+ | id | user_id | project_id | hours | date_range | +----+---------+------------+---...
0
votes
1answer
18 views

Return results of two MySQL query in only one

I am running two seperate queries to get statistics from 3 different tables: contacts campaigns sent_messages The first query counts all contacts that are marked as "stop" in the contacts table (...
1
vote
1answer
61 views

Using Window Function instead of Select sub-query for counting

I have to count the number of Orders a table, per Employee, for two criteria - "Late Orders". "Total Orders" The query includes Orders table and Orders table. They are joined together via the ...
4
votes
1answer
52 views

Why is the sum() of zero numbers not zero? [duplicate]

For the following query SELECT sum(c) FROM (SELECT 1 c WHERE false) t; I thought it takes the sum of zero numbers, and therefore should return 0. (Similarly, I expect, e.g. an array aggregate ...
1
vote
1answer
32 views

Error in update. I did not find the error. Why is max(C) not found?

(Translated from Portuguese with Google Translate) I created a table and made an update that should update column B equally to the percentage that C represents in relation to the maximum value of ...
0
votes
1answer
79 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
31 views

Another - ERROR: column “da2.dependency_device_name” must appear in the GROUP BY clause or be used in an aggregate function

I am attempting to aggregate dependency_device_name into one string, "Dependency List". Here is the error: ERROR: column "da2.dependency_device_name" must appear in the GROUP BY clause or be ...
0
votes
0answers
35 views

MySQL How to get first row from subquery when using aggregate function

This is my Query: SELECT COUNT(runs_scored) - SUM(IF(extra_type_id = 4, 1, 0)) AS Balls, SUM(runs_scored) + SUM(IF(extra_type_id IN (2 , 4), extra_runs, 0)) AS Runs, SUM(...
0
votes
1answer
113 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 ...
-1
votes
3answers
71 views

I need help with a SQL Query

I'm not so great with SQL queries and would like to know if someone would know the correct query to return the information below. I'm having a difficult time trying to figure this out. I'm using ...
2
votes
3answers
25 views

Get The Lowest Date if The other Column is Null

I have the following CREATE TABLE employees (employee_id int, from_date date, to_date date); INSERT INTO employees(Employee_ID,From_Date,To_Date) VALUES (1 , '1998-01-02' , NULL), ...
0
votes
1answer
37 views

Combing two queries in one

I have two tables, e.g. "Locations" and "Connections" "Locations" has values Id | Dimension --------------- 1 | 4 2 | 8 3 | 2 "Connections" maintains attributes Origin | ...
1
vote
0answers
22 views

Joining on nearest timestamp in MongoDB

MongoDB version: 3.2.11 Coming from using primarily PostgreSQL, I am new to MongoDB and am having trouble constructing complex queries in the required JSON format (where in SQL I would use CTEs). To ...
0
votes
1answer
49 views

Join 3 tables with where and sorting clauses

I have 3 tables, vendors, vendor_modelsand cameras. I am trying to get some values from db all of vendor_models but name from vendor too. Each camera has a reference of vendor_model as model_id. 10 ...
3
votes
1answer
101 views

Issue with Aggregate Function

I want to insert a tuple in my leggeapprovata table, but this table takes the attributes from 4 other tables. One of these 4 tables is the votazione table, from which it takes 6 attributes. Two do not ...
2
votes
0answers
53 views

Recommendations around managing large amounts of data that needs to be aggregated (SUM)

I am looking for alternatives to redesign a part of our system that needs to perform cumulative SUMs over large data sets of rows based on user and dates. How the system works We are running a web (...
3
votes
1answer
348 views

Query to obtain weighted percentile

Trying generate SQL to compute a weighted continuous value at a given set of percentile values (the 25%,50%,and 75% levels used below, but solution should allow for an arbitrary parameter level). In ...
0
votes
2answers
62 views

Get value increment during an hour of a day

I query YouTube Data Api for a list of most popular videos on a channel and then get their statistics, 4 times per hour (each 15 minutes, by cron). The data is stored in Postgres, but dumping it and ...
0
votes
2answers
35 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 | +---------------+-------+--------+----------+...
12
votes
1answer
487 views

Why is this stream aggregate necessary?

Check out this query. It's pretty simple (see the end of the post for table and index definitions, and a repro script): SELECT MAX(Revision) FROM dbo.TheOneders WHERE Id = 1 AND 1 = (SELECT 1); ...
1
vote
2answers
153 views

How can I count distinct columns inside a group?

I have the following query: SELECT DISTINCT name, surname, id_number, gender FROM table1; How do I filter by gender to get the gender breakdown of the results of the above query? Do I use a subquery?...
0
votes
1answer
85 views

How to get Row Value & Sum/ Aggregate in Same row? (SQL Server)

Just wondering if I have data like name score A 2 B 5 C 7 I would like to have a 3rd row name score total A 2 14 B 5 14 C 7 ...