Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

1
vote
1answer
38 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
29 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
13 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 | +---------------+-------+--------+----------+...
11
votes
1answer
423 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); ...
0
votes
2answers
52 views

How can i filter by gender from a subquery

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
21 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
36 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
59 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
37 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
30 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
50 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 ...
0
votes
3answers
149 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
19 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 ...
1
vote
1answer
32 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
80 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
74 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
16 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
37 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 | +------------------+---...
0
votes
0answers
31 views

Overwriting foreign key column values in JOIN statements

The foreign keys from referencing table and from original table are overwriting themselves in SQL JOIN's results. And that is happening in the order they appear in the SELECT list, later one prevails. ...
2
votes
1answer
150 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
51 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
42 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
104 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
42 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
24 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
255 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 ...
1
vote
2answers
276 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
77 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
56 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 ...
5
votes
2answers
301 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
71 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
50 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
0answers
23 views

sum time ranges

I'm trying to follow up the answer to this question: MySQL: sum time ranges exluding overlapping ones I tried making a comment to the accepted answer but my reputation is not enough, therefore I am ...
0
votes
1answer
120 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 ...
1
vote
2answers
357 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
677 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
56 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
639 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
31 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 ...
0
votes
3answers
192 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
1k 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
32 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
403 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
135 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
79 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
0answers
232 views

MongoDB Aggregation - Creating new arrays from a given array

i wish to create new arrays from a given array based on my aggregation results, the desired outcome from the following doc is to split the array into new arrays where the split point is the action "...
0
votes
1answer
32 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
0answers
15 views

get an array with a crosstab as multiple join

I have a ad_id from action_states u1 join with the table ad_media on u1.ad_id=ad_media.ad_id so basically as a return result i must have a set of array ad_media_id , it keeps giving me the results ...
0
votes
1answer
107 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 ...