Stack Exchange Network

Stack Exchange network consists of 175 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.

0
votes
1answer
24 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 ...
0
votes
0answers
26 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
28 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
65 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
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
24 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 | ...
1
vote
0answers
11 views

Joining on nearest timestamp in MongoDB

MongoDB version: 3.2.11 Coming from using primarily PostgreSQL, I am new to MongoDB and am having trouble constructing complex queries in the required JSON format (where in SQL I would use CTEs). To ...
0
votes
1answer
46 views

Join 3 tables with where and sorting clauses

I have 3 tables, vendors, vendor_modelsand cameras. I am trying to get some values from db all of vendor_models but name from vendor too. Each camera has a reference of vendor_model as model_id. 10 ...
3
votes
1answer
70 views

Issue with Aggregate Function

I want to insert a tuple in my leggeapprovata table, but this table takes the attributes from 4 other tables. One of these 4 tables is the votazione table, from which it takes 6 attributes. Two do not ...
2
votes
0answers
52 views

Recommendations around managing large amounts of data that needs to be aggregated (SUM)

I am looking for alternatives to redesign a part of our system that needs to perform cumulative SUMs over large data sets of rows based on user and dates. How the system works We are running a web (...
1
vote
1answer
118 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
34 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
16 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 | +---------------+-------+--------+----------+...
12
votes
1answer
443 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); ...
1
vote
2answers
86 views

How can I count distinct columns inside a group?

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
23 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
37 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
72 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
181 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
38 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
67 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 ...
1
vote
3answers
920 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
22 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
40 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
151 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
152 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
40 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 | +------------------+---...
2
votes
1answer
329 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
70 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
258 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
51 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
25 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
392 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
510 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
84 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
63 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 ...
6
votes
2answers
618 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
145 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
60 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
165 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
567 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
2k 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
70 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
656 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 ...
1
vote
3answers
314 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
2k 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 ...