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

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.

3
votes
1answer
31 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
34 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
0answers
13 views

MySQL 5.7: Left join json object aggregate

I would like to capture aggregate data in a pivot table along with both parents' columns. The two parents table, (table1, table2) contains the necessary information while the pivot table contains one ...
0
votes
1answer
16 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
61 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
58 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
14 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
0answers
43 views

GROUP BY max(value) from multiple days and keep all columns

I am using SQL server and I can't get the query I need. I have a table with several columns, the main columns are [Day], [Hour], [Cell]/[OBJECT], [UserTraffic] And example of the original table ...
0
votes
2answers
35 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
81 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
48 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
32 views

Adding an IF based comparison column

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
77 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
39 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
156 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
196 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
73 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
53 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
206 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
26 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
53 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 ...
1
vote
2answers
42 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
20 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
87 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
241 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
324 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
46 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
632 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
29 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
119 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
858 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
30 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
397 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
110 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
62 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
163 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
29 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
11 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
36 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 ...
3
votes
2answers
297 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
34 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
169 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
55 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?
-3
votes
1answer
33 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
325 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
91 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
54 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 ...