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
5 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
12 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
51 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 ...
1
vote
1answer
129 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 ...
1
vote
1answer
966 views

Rating count and group rates

I have a rating function, which is working fine but I need to count how many ratings of 5, 4 or 3 there are. SELECT COUNT(DISTINCT rating_rate) AS dis_count, COUNT(rating_post_id) AS total_rates, ...
0
votes
3answers
605 views

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

I have an ASSET_SUMMARY_VW view: +-----------------------+---------------+ | ASSET_TYPE | COST | +-----------------------+---------------+ | TRANSPORTATION | 1,000,000,000 |...
4
votes
3answers
3k views

Join Multiple Tables for Aggregates

How do I join multiple tables for aggregates with Sum and Count functions? The query I'm trying is as below: Select campaigns.id, campaigns.name, Count(landers.campaign_id) As landers_count, ...
0
votes
2answers
35 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
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
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
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 ...
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 ...
10
votes
1answer
91 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 ...
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
1answer
174 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
2answers
26 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')
13
votes
2answers
5k 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-...
1
vote
1answer
75 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
45 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
826 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
32 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. ...
3
votes
1answer
207 views

How to query chart data in a database agnostic way?

I have several tables with data like this: +---------------------+---------------+ | Creation Timestamp | Numeric Value | +---------------------+---------------+ | 2017-02-01 09:15:00 | 100....
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
3answers
48 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

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 (...
2
votes
3answers
268 views

Aggregate function to return any (first) encountered value

How do I tell PostgreSQL to return first encountered value instead of an aggregate column? Table "public.cache" Column | Type | Modifiers | Storage | Stats target | ...
0
votes
2answers
237 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
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
49 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 ...
3
votes
1answer
15k views

Merge multiple rows into one row with sql

In a source table each row represent hours for one day for a spesific Activity. How can I merge rows of days into rows of periods distinct on Employee, Year, Period and Activity with sql? Select from ...
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
29 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 ...
2
votes
2answers
1k views

How to aggregate transactional records by month year but based on subscription period not calendar months?

I have a table containing transactional events for customers: table: events | id | cust_id | date | amount | |------|----------|----------------------------|--------| | 78 ...
2
votes
2answers
1k 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 >=...
0
votes
0answers
31 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
74 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
2answers
4k views

MAX of a varchar column

I have a table with a column named price typed as varchar. I want the maximum value of this column. I am using this query: SELECT MAX(price) FROM product_management.`item_list` but the result is ...
56
votes
7answers
51k views

Writing a simple bank schema: How should I keep my balances in sync with their transaction history?

I am writing the schema for a simple bank database. Here are the basic specifications: The database will store transactions against a user and currency. Every user has one balance per currency, so ...
0
votes
1answer
106 views

Ratio per Group in PostgreSQL

If I have rows like this: lec1 major1 class1 10 lec1 major2 class1 40 lec1 major1 class2 30 lec1 major3 class3 35 lec2 major1 class3 15 lec2 major3 class3 10 And I need it to combine the ratio into: ...
6
votes
1answer
29k views

GROUP BY and ORDER BY problem

I have two tables like this: CREATE TABLE cmap5 ( name varchar(2000), lexemes tsquery ); and CREATE TABLE IF NOT EXISTS synonyms_all_gin_tsvcolumn ( cid int NOT NULL, -- ...
2
votes
2answers
793 views

MongoDB explain winning plan shows “EOF”

I've been working on optimizing my queries on some 2 million documents in MongoDB and I tried to use explain on aggregate functions, but it would display "winningPlan" : { "stage" : "EOF" }...
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 | ...