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
4
votes
3answers
1k views

Postgres - Trying to backup Functions only

I'm trying to follow Craig Ringer's answer to this question: https://stackoverflow.com/questions/13758003/how-to-take-backup-of-functions-only-in-postgres My command is: psql -U username -AT db_name ...
1
vote
1answer
270 views

How to change aggregate function without duplicating SQL (by using SQL)

In SQL Server 2016 I have a scenario where data will be processed according to different aggregation functions in a large GROUP BY ROLLUP. I would like to have a stored procedure that has a parameter ...
4
votes
1answer
5k views

Consolidate multiple rows into single row

In PostgreSQL 9.5, I have a table named reports: CREATE TABLE public.reports ( id BIGSERIAL PRIMARY KEY, id_station character(11) NOT NULL, date date NOT NULL, element character(4) NOT NULL, ...
2
votes
1answer
74 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 ...
4
votes
2answers
385 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 ...
0
votes
2answers
237 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 ...
2
votes
3answers
268 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 | ...
2
votes
3answers
921 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 Max(...
3
votes
2answers
6k 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
454 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
1k 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 ...
2
votes
1answer
48 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
1answer
50 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 ...
2
votes
2answers
4k 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 ...
4
votes
1answer
3k 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
125 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
106 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 table)...
0
votes
1answer
46 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 ...
1
vote
1answer
130 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 ...
7
votes
2answers
392 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 ...
0
votes
1answer
46 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 ...
6
votes
3answers
13k 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
424 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
4k 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 | ...
3
votes
1answer
662 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
91 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
108 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 into: ...
1
vote
1answer
866 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, ...
1
vote
1answer
393 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' ...
0
votes
2answers
545 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) ...
0
votes
1answer
26 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 would ...
-2
votes
1answer
2k 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 ( ( ...
5
votes
2answers
2k 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 ...
4
votes
3answers
1k 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 ...
2
votes
2answers
2k 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 ...
8
votes
2answers
8k 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 ...
3
votes
1answer
1k 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 ----...
3
votes
2answers
63 views

Get rows with minimum value

I have tables company: company_id | - - - - - - - - - - - - - - 1 | 2 | employee: employee_id | - - - - - - - - - - - - 1 |...
1
vote
2answers
67 views

Displaying a value associated with a maximum value

I have a PostgreSQL database for a supermarket (it's a toy problem) and I need to find which outlet has sold the most copies of each product and display it in a query which also shows the product name,...
0
votes
1answer
116 views

Multiple criteria for including / excluding rows

CLARIFICATION UPDATE Unfortunately we report based on the 3rd party application and database so we cannot modify the datatypes since we do not own it. To help clarify, This report basically shows us ...
0
votes
2answers
92 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 ...
1
vote
1answer
36 views

Help to retrieve data set

I have data set as below, for each Parent Record there can be multiple Cancel and Un-Cancels. I am trying to pull for each Parent, I am trying to find when it when it got cancelled and when it got un-...
5
votes
3answers
189 views

Aggregate discarding values in one column that haven't a match in another column

Say, I have a table representing colored and labeled items inside numbered boxes. Each box can not contain more than one item with a particular label, but items with the same label (and the same or a ...
0
votes
2answers
513 views

Calculating Margin in CASE Statement and GROUP BY Clause

I think I am running into an error in how I have chosen to aggregate my columns in a group by statement. I am working out Margin % and grouping into months using a case statement like so: SUM(CASE ...
0
votes
1answer
96 views

How to get first value from list of value

I have data as follows Id Col1 Col2 ================ 101 101 abc 101 102 def 102 102 ght 103 101 mgj 103 102 dkf 103 103 dfj I need output as follows Id Col1 ...
0
votes
0answers
36 views

Need help with Oracle Query for totaling fields with separate clauses, any feedback highly appreciated!

I am stuck on an issue that has been causing my mind to ramble for the last couple of days. DETAILS ABOUT REPORT This report is to identify the following details based off of the CUSTOMER_ID new ...
1
vote
1answer
55 views

Extract start and end per group of rows, where only the end can be identified

I have a table that looks like this. id date_from date_to description vehicle 1 2015-10-21 08:00 2015-10-21 10:00 GARAGE TO CLIENT 1 1 2 2015-10-21 12:00 ...
0
votes
0answers
66 views

Joining one table with multiple tables for aggregate functions possible?

I am trying to get this seemingly simple looking query to work : SELECT c.id, c.name, count(chc.id) as contacts_count, count(cha.id) as addresses_count FROM (customers c LEFT JOIN ...
9
votes
2answers
11k views

Efficient merging (removing duplicates) of arrays

I have two tables, left2 and right2. Both tables will be large (1-10M rows). CREATE TABLE left2(id INTEGER, t1 INTEGER, d INTEGER); ALTER TABLE left2 ADD PRIMARY KEY (id,t1); CREATE TABLE right2( t1 ...
3
votes
2answers
2k views

Postgresql: Pattern match against array elements?

Is there any way to do a pattern match against the elements of an array in postgresql (9.4 if the version makes a difference)? I have an aggregate function that, among other things, returns an array ...