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.

learn more… | top users | synonyms (1)

0
votes
0answers
32 views

SQL Server Join the same table twice with different filters [on hold]

I'm working on a query right now that requires me to filter joined data differently for multiple aggregate columns, so I'm joining the same table twice and applying a filter only one one of the joins. ...
4
votes
2answers
15k views

SQL query that concatenates values from duplicate rows in a single table

Let's say I have a table of employees, like this: Name Sex Role Bob M Developer Joe M QA Now, I have a problem with duplicated rows in this table. I will fix it ...
3
votes
2answers
60 views

Why am I getting this ANSI warning about NULL values and aggregate functions?

This is the exact warning message: Warning: Null value is eliminated by an aggregate or other SET operation. I understand what it means in general, i.e. that there's a null value in one or more ...
1
vote
1answer
33 views

Postgres - Querying Min, Max and getting associated columns data specific to those result of those rows

I have query in which returns averaged values by site and month. What I want to know is the min and max of those monthly averages by site and (the difficult part) the month when each occurred. Here ...
2
votes
3answers
60 views

Vendors with Most Invoices in State

I have the tables Vendors(VendorName, VendorID,...) and Invoices(InvoiceID,...) and I am trying to find vendors with the most invoices in their respective states. Please critique my work: SELECT ...
1
vote
2answers
36 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 | ...
-1
votes
0answers
32 views

SQL Server Aggregate Function: “Get First Available”?

Does anyone know of a "get first available" aggregate function for SQL Server 2014? I would like to use such a function on a dynamic pivot table to speed up performance. The situation is that I only ...
0
votes
1answer
16 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 ...
0
votes
0answers
33 views

MySQL: Calculations and Triggers across multiple tables

I'm new to sql and I'm having trouble with a calculation involving two of my tables. I have a table, payroll, which looks something like this: CREATE TABLE payroll ( id INT(6) UNSIGNED AUTO_INCREMENT ...
1
vote
1answer
17 views

PostgreSQL ntile() partition

i have a point grid with population values. each point has an id and population value. i also have an state_id which say in what state the point is. now i want to calculate the percentile ntile(100) ...
3
votes
1answer
63 views

Finding hash match aggregate

After reading following blogs, I understand that hash match aggregate causes blocking. Using appropriate indexes it can be made as stream aggregate. Blocking/non-blocking aggregate operators The ...
2
votes
1answer
68 views

Aggregate count over variable number of json keys

How can one get a list of json objects like this: [ {"band": "beatles", "bass": 1, "drums": 1, "guitar": 2}, {"band": "metallica", "bass": 1, "drums": 1, "guitar": 2, "vocals": 1} ] from ...
0
votes
2answers
183 views

sqlite group_concat error while executing query

I have table: CREATE TABLE www ( Col_1 VARCHAR, Col_2 VARCHAR ); select count(*) from www; --1496 And I have error: "Error while executing query: aggregate function are not allowed in ...
1
vote
1answer
151 views

Type with nested table in oracle

I want to create a type with a nested table. I have a table called assignment_tbl which I want to put a type assignment_status. This assignment_status contains primitive types but also contains a ...
2
votes
1answer
30 views

How can I improve my query, using joins & improving performance?

I have a really smelly query which I'm not sure how to simplify, this is quite an intensive query. I have tried various union and inner joins in an attempt to simplify it and my biggest problem is ...
1
vote
2answers
410 views

Calculating average with AVG for a list of numbers

This is probably a newbie question (bear with me) but how can I calculate the average for a list of numbers using SQL and the AVG function? I mean, I can add together numbers in SQL like: SELECT ...
1
vote
1answer
40 views

Strategies for reducing data to summaries within a table over time?

I have a large data set where large numbers of entries differ only by timestamp. In the short term it's beneficial to maintain individual entries so that we can correlate by time for individual data ...
1
vote
1answer
59 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 ...
2
votes
1answer
24 views

How to count multiple values of a column as one group? [duplicate]

I have a table main with the following rows: id | name | rank ----+--------+------ 1 | Ali | a 2 | Sami | b 3 | Khan | c 4 | Kamran | d 5 | Imran | e 6 | Asad | a 7 | ...
0
votes
1answer
22 views

Aggregate function to count occurrences of specific word

I have a table upon which I'm performing a GROUP BY statement. One of the columns in my table can contain one of 5 different words (essentially an enumerated type). Is there a way I can use an ...
2
votes
1answer
55 views

Grouping data by year based on two timestamp columns

My data table has the following columns: id INTEGER, name TEXT, created TIMESTAMP, deleted TIMESTAMP I want to product a report of the count of each name (which can appear several times in the ...
1
vote
1answer
48 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 ...
0
votes
1answer
38 views

Average of last 4 runs of a product on SQL Server 2008

I'm looking to do the exact same moving average from this post: Average of last 4 runs of a product but now on SQL Server 2008, which doesn't support the extended window aggregate functions. Is ...
0
votes
2answers
56 views

How to find the sum and overpayment of three table values

I need to find the each client sum of TotalAmount in Invoice_TBL and sum of TotalPaid in Payment_TBL. The below i have mentioned three tables. Retrieve query answer should like below image from the ...
0
votes
0answers
32 views

MongoDB slow aggregation for 30 millions of documents

I have MongoDB cluster that include 3 Shard servers based on m3.large type, config server and mongos. There I have a database on 30G with one shard collection with 30 millions of documents. Each ...
0
votes
0answers
33 views

Mongo aggregation query on time series data

I am storing some user frequency data on site. Data looks like this { "uid" : "5b2db719-1943-408f-ec06-f88f663f5d56", "pgAccessTime" : NumberLong("1457073369030"), "day" : ...
4
votes
1answer
102 views

Total hours worked per employee per day including overnight sessions

I have a table like this: CREATE TABLE Table1 (ID int, empid int, time datetime, state int); +--------------+---------------------+-----------------+ | empid | time | ...
2
votes
3answers
749 views

Rolling count of total transactions over time

I need to get a set of total transactions over time on a weekly interval. Essentially I need a total-transactions-to-date column. When I group by WEEK(Date) I get the amount of transactions for that ...
0
votes
1answer
37 views

How do you get a count for an aggregated row?

I am getting sums for a set of rows, There is an id from each row that relates to 0 to many rows in another table. I am having trouble getting that number into the dataset. So here is what I have ...
7
votes
2answers
377 views

Average of last 4 runs of a product

I need to show the average weight of a product over its last 4 production runs. I'm not sure how best to describe it other than an example: Let's imagine I have the following table that lists a ...
3
votes
3answers
129 views

Calculate rolling sum over 7 consecutive day period on PostgreSQL

I need to get the rolling sum over a period of 7 days for each row (1 row per day). For example: | Date | Count | 7-Day Rolling Sum | ------------------------------------------ | 2016-02-01 | ...
1
vote
2answers
47 views

An aggregate may not appear in the WHERE clause

I'm attempting to execute the following query to return a data set where the Updated Date is the most recent. I'm thinking my joins are causing the issue, but I'm not sure, and have not made any ...
3
votes
1answer
85 views

Add a total quantity column for groups of rows

I have an Items table and an Inventory table. An Item can belong to multiple Inventory records. I'm trying to return a list of ALL Inventory records but with their quantity in a new column. For ...
1
vote
1answer
72 views

Group rows by running aggregate

I have a set of transactions that can either be payments or purchases. The purchases can be of different types (e.g. 'ApplePurchase', 'OrangePurchase'). I will select some payments by their coupon ...
0
votes
1answer
12 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 ...
0
votes
0answers
22 views

Is there a way to automatically set triggers to update a field that is computed as an aggregation of other fields?

Let's say that I have these tables: user, task, and submission. A user can create a submission which will be scored by the system, which will assign a value to submission.score. Now, everytime a ...
2
votes
2answers
178 views

Calculating Running Average using Over clause in SQL server

My table looks like this: CREATE TABLE [dbo].[TimeSeries]( [LOCID] [int] NOT NULL, [Date] DateTime, [YEAR] int, [MONTH] tinyint, [RTT] [int] PRIMARY KEY ( [LOCID] ASC,[Date] ASC) ) It contains 12 ...
1
vote
1answer
45 views

Multiple to_json(array_agg), separate joins

I'm building a messaging system, and trying to get a list of messages and their replies; and also join the participants of the message threads. So I'm shooting for messages = [{id, subject, body, ...
2
votes
2answers
15k views

how to calculate difference of first row and last row fields value in each group

I have table with structure like this: +-------+------------------+ | Value | Date | +-------+------------------+ | 10 | 10/10/2010 10:00 | | 11 | 10/10/2010 10:15 | | 15 | ...
1
vote
1answer
67 views

Filter created_at and count distinct minutes

I have this query to get records from a database: SELECT camera_id, count(*) AS snapshot_count FROM snapshots WHERE created_at >= timestamp 'yesterday' AND created_at < timestamp 'today' ...
3
votes
1answer
7k 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, -- ...
3
votes
2answers
82 views

Array integer[]: how to get all distinct values in a table and count them?

I'm not so good with SQL (PostgreSQL). Here is what I want to do: I have a table, fields: id SERIAL inet INET ports integer[] id | inet | ports ----+------------+------------ 2 | 1.2.2.1 ...
-1
votes
2answers
79 views

Calculating multiple medians

I have defined function with table value parameter which returns median as per values passed to it. Function defined as: CREATE FUNCTION [dbo].[fn_GetMedian](@List TypeMedian READONLY) ...
4
votes
2answers
398 views

How can I pass column to function in sql?

I'm calculating median as: DECLARE @TEMP TABLE ( ID INT ) Select ( ( Select Top 1 ID From ( Select Top 50 Percent ID ...
0
votes
1answer
15 views

aggregate on join latest value

This is a problem I often get stuck in and I hope there is a solution. Let's build an example: Table products: product_id, product_name Table Prices: product_id, valid_since, Price Lets say I ...
5
votes
1answer
981 views

Calculating Median Value within a Group By

I have a Stored Procedure which calculates the Mean duration of a request / response cycle within 10 minute intervals. This works well and suits my needs for plotting charts. What I would like to do ...
-2
votes
1answer
65 views

why unable to execute table-valued function?

Here is table-value function I have declared like: CREATE FUNCTION fn_GetMedian(@List TypeMedian READONLY) RETURNS INT AS BEGIN RETURN ( Select ( ( ...
4
votes
3answers
506 views

Avoid double query on the same table

I have two queries which are doing basically the same thing, but have different grouping, the first query (query 1) is used to populate a chart, and the second to populate a table. Query 1: SELECT ...
1
vote
2answers
95 views

Query on bridge table

I have three tables dealing with books: Authors, Titles of books written and a bridge table TitleAuthor, from from which I am trying to find all books (Titles) with more than one author. These are the ...
4
votes
1answer
49 views

Group and count array elements using intarray

I am working on a Postgres 9.4 project with the intarray extension enabled. We have a table that looks like this: items ------------------------------------- id name tag_ids ...