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
1answer
55 views

SQL query with GROUPING SETS to get subtotals with a HAVING clause

Let's say I have a (fictional) table City with the following columns: Country: string State: string City: string Population: integer I want a query that shows the total population by State, by ...
5
votes
1answer
229 views

If at least a negative number, sum; otherwise just show

I have a need to sum values together only if there is a negative value present in a partition. If there are no negative values in a partition, it should just output the row. Here's what I have right ...
1
vote
1answer
219 views

MongoDB aggregation $unwind and 1NF

I have a basic rhetorical/theoretical question for people experienced using both MongoDB and RDBMS. Surprised SO has no Data Munging or Data Engineering forum. In making an aggregation pipeline in ...
1
vote
1answer
19 views

Finding average of multiple delta times

I'm looking to find the average interval between successive rows across the whole query - delta-previous in below example: SELECT *, "RecognitionTimestamp" - lag("RecognitionTimestamp") OVER(ORDER ...
0
votes
2answers
43 views

Using Select Alias in where clause

I have a table games +------------------+----+ | title | id | +------------------+----+ | Need for Speed | 1 | | Far Cry | 2 | | Assassin's Creed | 3 | +------------------+---...
4
votes
1answer
490 views

Select last non-null value for given partition - Postgres 10

I have a table with the following structure: date city cntry free occ 2018-08-13 16:30:00 2 12 5 2018-08-13 16:30:00 2 12 0 2018-08-13 14:30:00 2 12 1 ...
0
votes
2answers
112 views

GROUP BY clause / Aggregate function error [closed]

I am having a problem creating a Table View: DROP VIEW IF EXISTS product_variation_stock_view; CREATE VIEW product_variation_stock_view AS SELECT product_variations.product_id AS ...
0
votes
1answer
43 views

Adding an IF based comparison column [closed]

I have a table which contains the following columns Table6 ID Place Name Money, From this I am trying to create a table which contains the following column Place ,Avg_Money_Place ,High_or_Low , the ...
-2
votes
2answers
658 views

Find maximum number of concurrent logged in distinct users

I am looking out for a query providing month and year and show 4 (or 5) weeks of the selected month, and for each week it should show the maximum number of concurrently logged in users during this ...
1
vote
1answer
59 views

How to get aggregate data from a dynamic number of related rows in adjacent table

EDIT: Unknowing of the rule that prohibits cross-posting, I also asked this on Stackoverflow and chose an answer over there. Since there's another (fully working) answer in this thread though, I won't ...
0
votes
1answer
26 views

Choose record based on varying condition

I have a table that a primary key that can have one to two different status' associated with it. It looks like this: Key Status 123 Book 123 Change 122 Book 121 Book 121 Change If there is a record ...
1
vote
1answer
611 views

Aggregate a JSON doing an average on values without defining keys

I would like to know if there is a way or an existing function to perform an aggregation on a JSON field by doing the average of values if the key appears multiple times with PostgreSQL. The json's ...
2
votes
2answers
906 views

Add cumulative sum to time-series query PostgreSQL 9.5

I wrote the query that gives me time-series over some date range and interval that shows revenue for each time interval: SELECT interval_date, coalesce(campaign_revenue,0) AS campaign_revenue,...
2
votes
1answer
94 views

Aggregate additional values to min and max

This is a follow-up question to: Make custom aggregate function easier to use (accept more input types without creating variants) This SQL statement is given: SELECT c.name AS commodity_name, ...
3
votes
2answers
73 views

Make custom aggregate function easier to use (accept more input types without creating variants)

Recently I wrote a custom aggregate function in postgres that would return a specific column for the row that matches the max/min aggregate using a different column. While the code in itself works ...
7
votes
2answers
1k views

How to apply ORDER BY and LIMIT in combination with an aggregate function?

A fiddle for my question can be found on https://dbfiddle.uk/?rdbms=postgres_10&fiddle=3cd9335fa07565960c1837aa65143685. I have a simple table layout: class person: belongs to a class I want to ...
0
votes
2answers
27 views

SQL Query with Aggregate Count, where at least 1 of the counts has a value in a field

SELECT PERSON_ID,COUNT(APPL_ID) COUNTS FROM APPLICATIONS GROUP BY PERSON_ID ORDER BY COUNTS DESC The above query returns all persons who have applied for all time (essentially a count of the whole ...
0
votes
2answers
301 views

Postgres - using aggregate functions to return a different column?

I currently have a table containing raw temperature data that looks like this: I have written a query to find the min, max and avg of this data grouped by the date of the timestamp: drop table ...
2
votes
2answers
81 views

MySQL: Reference aggregate function in conditional select

I'd like to reference aggregate function like this: Sample Data: +-----------+----------+-------+ | member_id | group_id | score | +-----------+----------+-------+ | 1 | 1 | 12 | | ...
0
votes
1answer
218 views

Calculate difference between SUM over last 30 days and the SUM for 30 days preceding this period (days 30-60) in Postgres

Every day we store the number of jobs posted for hundres of thousands of companies. We need to query: The SUM of jobs_posted for the last 30 days for each company; The DELTA between jobs posted in ...
2
votes
2answers
803 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" }...
-1
votes
4answers
5k 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 ...
1
vote
0answers
95 views

Poor UnionAggregate (CLR aggregate) performance - CPU cost hint?

I'm trying to use SQL Server Spatial to work with data related to geographic regions defined by a third party, and am having trouble getting acceptable performance. The following query takes quite a ...
5
votes
2answers
678 views

Where clause over multiple rows, GROUP BY

I have the following table structure AccountID Property Value 123456 Status Active 123456 City Los Angeles 123456 RegistrationDate 2018-05-...
0
votes
1answer
34 views

how to retrive data from InvoiceNo

select Month(IH.InvoiceDate) as Month, COUNT(ID.InvoiceNo) as Count, COUNT(ID.InvoiceNo) as DirectMobile, COUNT(ID.InvoiceNo) as Preselling, COUNT(ID.PerformaInvoiceNo) as DirectWeb ...
1
vote
3answers
419 views

Calculate percent of roads (by length) where condition is >=15

I have a road condition table: CONDITION LENGTH 2 5 4 10 6 15 8 20 10 25 12 30 14 ...
1
vote
1answer
3k views

SQL, sum one column where another column is the same and display as the third column

I have the following data: a b 1 23 1 14 1 3 2 45 2 16 and what I would like is to sum column b where column a is the same and have this displayed in a third column, like so: a b c 1 23 40 ...
1
vote
1answer
34 views

select rows where values equal the result of count(*) group by?

I have a table like this: Each keyword should have 4 pieces of data. I want to select the keywords that have less than 4 pieces of data (less than 4 rows). I used select count(*) from mytable ...
10
votes
2answers
412 views

Get incremental counts of an aggregated value in a joined table

I have two tables in a MySQL 5.7.22 database: posts and reasons. Each post row has and belongs to many reason rows. Each reason has a weight associated with it, and each post therefore has a total ...
0
votes
2answers
219 views

Use GROUP_CONCAT() to generate polygons in mysql

I have a table that has columns zone(name of the zone which is a string), longitude (decimal), latitude (decimal), logical order (integer). logical_order indicates the order in which the coordinates ...
0
votes
2answers
197 views

How to include empty string in string_agg? [closed]

I've done a groklearning exercise up until the point of: Make sure to list all properties, even if there is some unknown address information; replace any missing parts with an empty string Could ...
0
votes
1answer
39 views

How to check if list of values in one column match list of values in another column (aggregations)

Given two results consisting of single key|value pair (you can imagine those are in CTEs), I want to join and group them by key, aggregate their values and return two different things: a) those keys ...
0
votes
1answer
715 views

Combining 2 temp tables to aggregate into a single table

I'm working on a SP that takes a lot of data, separates it into two temp tables and unions those two together. It works, but right now it's giving me a different line for each of temp tables, but I ...
4
votes
2answers
589 views

Speeding Up Query on a Time Series Table. It's Slow to get (MAX - MIN) Aggregation Value

Question How to improve this query to increase the speed of the query shown below from 30+ seconds to milliseconds? I am using PostgreSQL: v 9.6.6. Context I have a time series table buildings....
2
votes
2answers
79 views

Aggregate Same Column, Different Tables

I have the following schema: table_1 id, updated_at table_2 id, updated_at table_3 id, updated_at and in total there are about 12 tables that I want to get the updated_at column and determine the ...
1
vote
2answers
675 views

“Column does not exist” with INNER JOIN and MAX query in ORDER BY

I'm using PostgreSQL 9.6 with ActiveRecord. I'm trying to get all customers sorted by a specific provider. The relationship between models is: customer has many orders, which belong to a provider. ...
4
votes
1answer
122 views

Spatial query to get enclosing radius/polygon of result set

Is it possible in SQL to run a query of spatial data, and then just return a single polygon, or a radius, that encloses, aggregates, all of the spatial data that was in that query into one feature?
-2
votes
1answer
43 views

Ordering an aggregated query

I have a table consisting of a pk, an id, an integer value and a type consisting ~160,000,000 rows. Consider these data in the table: CREATE TABLE foo(pk,id,value,type) AS VALUES ( '1' , '1', '...
1
vote
2answers
895 views

Problem with only_full_group_by

I was trying to solve this HackerRank problem using MySQL and here's what I came up with: select id, wands_property.age, min(wands.coins_needed), wands.power from wands join wands_property on ...
0
votes
1answer
945 views

group by excluding a column

I have this code: insert into [dbo].[NGC_Agent_Intervals] SELECT AgentID, date, [Hour] = DATEPART(HOUR, RSRange), hour_quarter = CONCAT(DATEPART(MINUTE, RSRange) ,'-', DATEPART(...
0
votes
1answer
68 views

Substract two database entries based on value of a certain field

Good evening everyone, I am looking for the most elegant way to substract two values of one column based on the value in another column. Until now I could only find hacky and unreadable solutions: I ...
1
vote
0answers
52 views

Combining two event tables into one postgres view

I have two tables, events and event_logs. The events table stores the theoretical data on a recurring event whereas the event_log stores specific information on a particular datetime. Event +-----...
1
vote
2answers
7k views

Group by multiple columns, agregate others and select all in SQL Server

I am using SQL server and I can't seem to construct the query I want. I have a table with several columns, among them are PARAMETER_NAME, GW_LOCATION_ID, Report_Result, DETECT_FLAG I want a ...
1
vote
1answer
176 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
votes
2answers
64 views

How to implement GROUP BY aggregation with holistic aggregation if partitions do not fit into main memory?

Let's say I want to compute the median of some data given some grouping attributes G, e.g. the median price of all product sold daily (assuming a star-schema) SELECT T.day_id, MEDIAN(price) FROM ...
2
votes
1answer
375 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
2answers
91 views

SQL query - How to select to display group, group total, grand total?

I've seen some advice but want to know the best way to select a group, group total, grand total from a data table WITHOUT using a subquery or unnecessary join. My initial thought was something like ...
0
votes
1answer
18 views

Using OLAP-specs to compute selective running totals

Let's say I have a table with columns id, subid, state, value, where (id, subid) is a unique tuple, state may be 0 or some value <> 0, and value is just some number. I want to build a query that ...
1
vote
1answer
68 views

Will PostgreSQL optimize query when the same aggregate usage?

SQL would not let use aliases (s,d) in other columns, so, my query is: SELECT employe, sum(salary + bonus) AS s, sum(debt) AS d, (sum(salary + bonus) - sum(debt)) AS total FROM ...
1
vote
1answer
382 views

Create hstore from key-value result set

I have following columns: key | value ----------- foo | 1 bar | 2 Is there a recommended way to turn this into hstore? {foo => 1, bar => 2}