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
1
vote
0answers
6 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
16 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
53 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
38 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
81 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
22 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
94 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
48 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
17 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
56 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
27 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
40 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')
1
vote
1answer
85 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
49 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
828 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
34 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
49 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
17 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
17 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
60 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
50 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
64 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
30 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
32 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
82 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
36 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
20 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
96 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
312 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
57 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
29 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
484 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
144 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
53 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 ...
0
votes
1answer
37 views

How to count items in specified ranges in one query?

I have this table: Id | Username | FollowersCount 1 | user1 | 1500 2 | user2 | 700000 3 | user3 | 94000 4 | user4 | 117000 I want to find the number of users who have less than 50k ...
1
vote
2answers
105 views

Strip longest common suffix and concat in Postgres

Given a table t: id | name ------------ 1 | abcfug 1 | deffug 1 | hijfug 2 | etc How can I do something like: select string_agg(strip_lcs(name), ', ') from t where id = 1 returning: abc, def, ...
0
votes
1answer
349 views

merge json online postgresql 10

I have the following tables: create table tags ( id bigserial primary key, file_id bigint, key varchar, num bigint, str varchar) create table meta_info ( ...
0
votes
1answer
48 views

JOIN tables and return count even if it is 0

I have this query: select settings.customer_id, current::jsonb->'alarms' as alarms, from settings join (select customer_id, current::jsonb->'alarms' as alarms from settings) as res on res....
3
votes
1answer
109 views

How to pass an array of arguments to User Defined Aggregate in SQL Server

I have a custom aggregation (UDA) and I want to pass an array of arguments like so: select dbo.MyAggregation( t.type, N'My aggregation property', t.param1, t.param2, t.param3 ) from ...
1
vote
3answers
3k views

How to select multiple values into an array and loop through? (postgres 9.3)

I have simple table for the sake of argument. I have a function that selects ids and loops through them called loop_test. I can select an array of ids and loop through them, causing my changes in a ...
0
votes
1answer
25 views

MySQL, comparison between, tables with not all records common, on 2 time intervals

I want to make comparisons on the volume of sales, turnover and margin of all product sales between 2 time intervals (2017-10-1 to 2017-10-31 ) and (2018-10-1 to 2018-10-31). for example: Product ...