# 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.

426 questions
Filter by
Sorted by
Tagged with
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....
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 ...
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 ...
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 ...
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, ...
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 |...
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, ...
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,"...
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 ...
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]+...
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 ...
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 ...
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 ...
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":...
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 | ...
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.
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 ...
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 ...
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')
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-...
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,...
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 ...
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 ...
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 ...
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. ...
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....
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 | +----+---------+------------+---...
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 ...
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? ...
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 (...
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 | ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 >=...
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(...
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 ...
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 ...
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 ...
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 ...
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: ...
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, -- ...
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" }...