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)

1
vote
2answers
67 views

Combine multiple similar queries into one

I need to calculate a weighted average using the following table. I have to calculate this number for metric_1 up to metric_10. All the metric columns have a value from 1 to 5. ...
1
vote
1answer
53 views

How to properly aggregate?

I have a table for logs with over 6 million records. I want a query that shows me all the occurrences where the src goes to the same destination/port. I tried this: SELECT src, dst, dstport, ...
1
vote
0answers
111 views

Pentaho Kettle (Data Intergration) MongoDB Aggregation

I'm using kettle v5.2 which support the aggregation pipleline in MongoDB when using MongoDB input the query works for small data set but I need to use option allowDiskUse to the query can't figure ...
1
vote
1answer
136 views

MongoDB Insert Performance

I have some data which are aggregated in chunks and later pushed into another collection. The problem is that aggregated data pushing to another collection is very slow and takes from few hours and i ...
1
vote
1answer
167 views

Select two aggregate functions on the same column using different criteria - performance

Dear stack exchange users, first question here. The database in question is a service database where a user hires a service. Now I would like to show the person that was hired an overview of how many ...
1
vote
0answers
147 views

Multischema view for postgres , querying multiple databases

Im managing SaaS app where each customer has a separate database (Postgres 9.3) . I need to figure out the best way to aggregate and query this data ( READ-only ). I know postgres has problems with ...
1
vote
0answers
93 views

Join and aggregate result and insert blanks for missing appendants

Consider these two simplified tables: CREATE TABLE "sources" ( "id" serial NOT NULL, "name" text NOT NULL, CONSTRAINT "sources_pkey" PRIMARY KEY ("id") ); CREATE TABLE "values" ( ...
1
vote
1answer
999 views

SQL hourly data aggregation in postgresql

I am a newbie with database so I'm seeking your help with this one. I have a table containing time series data. 2012/01/01 00:10, 10 2012/01/01 00:30, 5 2012/01/01 01:00, 10 2012/01/01 01:40, 10 ...
1
vote
1answer
96 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 ...
1
vote
1answer
452 views

How to aggregate pairs of id values into groups

I have a table with two id columns id1 and id2. Each row shows that 2 id values have been linked together. My actual data has both id columns as varchar(20) but I have used integers in the example ...
1
vote
0answers
185 views

MySQL aggregation count performance

I have a table with about 30 million records, and I'm doing a query where I can group by field Year and count the results for that year. The table structure looks like: id | year | amount The ...
1
vote
1answer
1k views

How to get MySQL to iterate a subquery in an aggregate select?

Because the vocabulary describing what I'd like to do can be so disparate, I haven't had any luck figuring out why MySQL 5.6 is doing what it does. Consider the following two simple queries: #1 ...
1
vote
0answers
509 views

How to calculate running total based on dynamic values?

I need to calculate the running total in which the target column is changing along with the time. Its something which aws does. Charging based for only what you use. So how do I aggregate sum of ...
1
vote
1answer
14 views

Is it possible to use a column value as the argument to a hypothetical-set aggregate function?

Say I have a table like the one described below. Table "public.foo" Column | Type | Modifiers ...
0
votes
2answers
6k views

How to select rows which have max and min of count?

I have the following table in SQL Server 2012: CREATE TABLE [MyTable] ( [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWID(), [MyGroup] INT NOT NULL ); I want to output a table of the ...
0
votes
1answer
95 views

SQL Server's aggregate functions ignore nulls. How do I correct this?

According to MS's knowledgebase, Except for COUNT, aggregate functions ignore null values. Does anyone know how I can get it to work correctly, that is, return a null if it attempts to ...
0
votes
2answers
111 views

compare dates in results

I'm looking to compare the dates in the result set Example: SELECT * FROM tbl WHERE foo=bar Output id, datetime, name 1, 2014-01-01 18:13:45, test 1 2, 2014-01-01 19:01:32, test 2 3, 2014-01-01 ...
0
votes
2answers
46 views

assign selected columns to a variable

Select sum(column_a) as a, sum(column_b) as b round(a / b) From table Group by column In the example above I am summing two column, and I would like to use those 2 results in calculating ...
0
votes
1answer
114 views

How to include in the result of a `SELECT … GROUP BY …` all the other columns that are functionally dependent on the grouping ones?

I'll base this question on a toy example. Let this be table A: A ------------------------- U | V | W | X | Y | Z ------------------------- a | b | c | 1 | 6 | 8.3 a | b | c | 1 | 4 | ...
0
votes
2answers
192 views

Query to return fields of distinct values per key

In the process of building a data warehouse we are creating some aggregation tables for a reporting engine. We would like to build a single aggregation table from the fact table that can exist as list ...
0
votes
2answers
53 views

Count of distinct values of a table using sql

name number ============= lin | 8 lin | 8 lin | 8 rob | 8 rob | 8 rob | 8 lin | 8 lin | 41 lin | 41 rob | 41 and I want the result like name | Count of 8 | count of 41 ...
0
votes
1answer
53 views

HAVING ANY in Postgres

I'm looking for the right syntax to filter groups (i.e. groups of rows that have been GROUP BY'd) where at least one row fits a certain condition. For example if I wanted to select a group where at ...
0
votes
2answers
121 views

Aggregating sum day by day from multiple joins returns unexpected results

I'm trying to make a table where the task hours and group hours are aggregated day by day. I'm able to get the wanted result for both tasks and groups, but when I try to get them in the same query, I ...
0
votes
2answers
125 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 ...
0
votes
1answer
614 views

group by clause without aggregate function

I've always assumed GROUP BY was designed specifically for aggregate functions and in all other circumstances you should use ORDER BY. For example, we have three tables: orders, shippers, and ...
0
votes
1answer
84 views

How to add up a column depending on data from another column

I have a table which has results and a table that has band_name and band_id. Now I need to create a query which will add up the total points for each band and display the band name and ID, so far I ...
0
votes
2answers
94 views

Grouping data checking against top values

I've got data in this form: CallNum Value Accepted -------------------------------------- 971374 81.482204444473609 True 971374 83.783551111089764 False 971374 97.936875555547886 ...
0
votes
1answer
22 views

postgresql double count query

I have a table with 3 columns: key, status and rank. status contains numbers and rank contains t or f. key is auto numbered and irrelevant here. my goal is to count how many in each status are t and ...
0
votes
1answer
65 views

Put Postgres query result in array

How can I put an sql query result in a string array with Postgres? I have some attributes in the column 'zone' of the table 'mytable' and I must put them all in a String[] areas; This is my first ...
0
votes
1answer
101 views

Group by without temporary table and filesort

My aim is to make the following query faster. SELECT SQL_NO_CACHE Links.id,Links.link,sum(ID_Links_Words.count) AS wordCount FROM Links LEFT JOIN ID_Links_Words ON Links.id = ID_Links_Words.link ...
0
votes
2answers
25 views

Adding counts inside a COALESCE

So I have a continuation of this question. New requirements have thrown in a third table. So here's the breakdown of how the tables relate: TabA(TabA_id, desc) TabB(TabB_id, TabA_id) TabC(TabC_id, ...
0
votes
3answers
879 views

Getting around the constraint “column must appear in the GROUP BY clause or be used in an aggregate function”

I'm using Postgres, which enforces the constraint that all columns in a SELECT...GROUP BY must appear in the GROUP BY clause or be used in an aggregate function. Lets say I'm modelling peoples' cars, ...
0
votes
1answer
124 views

Slow running aggregate of aggregate query

I am looking for the total balance for all accounts that had a negative balance as of a specific point in time. I've already got the code below, and it works, but works horribly. The transactions ...
0
votes
1answer
263 views

PL/SQL group orders by date and sum the total [closed]

I need to group orders by date and sum the total, My table looks like this : TEST_DATE TEST_ID - NUMBER SUCC_ID - NUMBER I need to group by date and sum the total, this is my query: SELECT ...
0
votes
2answers
571 views

Filling in missing dates in record set from generate_series()

Consider: with days as (select day::date from generate_series(date '2013-01-01', date '2013-01-01' + 365, interval '1 day' day) day ) select 'Inspections'::text as data_label, count(i.reporting_id) ...
0
votes
1answer
525 views

nesting MAX for SELECT

I have a table, containing TV series episodes: series: | series_id | season | episode | title | 1 | 1 | 18 | xxx | 1 | 2 | 3 | xxy | 3 | ...
0
votes
1answer
370 views

Nested query not recognize external table

i need to a query that show week by week the total amount of imports of any company and in addition add a ranking position depending on that amount. I've made this query but the problem is the inner ...
0
votes
1answer
472 views

Column 'Comments.Text' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

They have explained the root of the problem very well and even advised to use max aggregate as a solution. But, lets take the example group_id item_id comment -------------------------------- ...
0
votes
1answer
432 views

Index for ORDER BY on aggregate in MySQL

I'm working with the following query: SELECT *, SUM(entries_count) AS entries_count FROM leads WHERE promo_id IN (1, 2, 3) GROUP BY user_id ORDER BY entries_count DESC LIMIT 25 OFFSET 100; It's ...
0
votes
0answers
33 views

How can I write a query to aggregate multiple strings into one field? [migrated]

I am trying to merge three tables. The first table has id and data_name, second table data_id, option_id and property, third table user_id, data_id. For example: first table +----+-----------+ | ...
0
votes
1answer
13 views

How to use PostgreSQL bit_or function to aggregate column?

I wish to use PostgreSQL's bit_or function on several rows of a single column of my table, but I can't figure out the proper usage for this. Suppose I have bitwise flags in my column flags, and the ...
0
votes
0answers
12 views

How to calculate sum of a column in a table accessed with multiple joins and where condition

I am unable to find correct sum of vaccinated based on campaign id. no matter which campaign I choose to get sum of vaccinated on the basis of that campaign id it always returns the same data. Please ...
0
votes
1answer
49 views

Optimization for aggregation and grouping with MySQL

I have an application that is pulling data on a daily basis from numerous APIs, storing it and then presenting it on the front end in various charts, tables, etc.. I'm having some issues avoiding ...
0
votes
1answer
34 views

Restricting multiple records to one by preference?

Below is a query I'm using to extract the best Price1 amongst several distributor's pricing tables. SELECT products.UPC, T2.Price1, vProducts_PriceQty_Union.Price2, ...
0
votes
1answer
93 views

SQL Server Function For Calculating Average Excluding Outliers

I'm looking for a function that will let me take an average and exclude outliers. Basically something where: 1, 2, 1, 2, 7 Would average out to 1.5. I'm going to implement this in a financial ...
0
votes
1answer
84 views

Select on multiple tables with group by

Using Postgres 9.3, I have problem with a select over few tables. I have simcards stock and I would like to get something like this: | network | supplier | total topups (EUR) | 1st topup qty| | ...
0
votes
0answers
20 views

parent / child aggregate column

To start with, here is my fiddle. I have a parent / child relationship setup, I have them ordered by who they belong to, and I want to have a column called lastUpdated in which the parent has the ...
0
votes
1answer
51 views

Postgres Nested WHEN Aggregate Function

In PostgreSQL (version 9.4) I am trying to construct a query to determine in various tranches how many times various numbers appear in the data set between ranges. When I query group-ing by ...
0
votes
1answer
18 views

Adding a column to aggregated data where new value only applies to a subset of the aggregation

Having trouble explaining this. I have a table like this: Label | Value A | 3 B | 1 C | 4 And another like this: Label | Value | ID A | 1 | ID1 A | 1 | ID2 C ...
0
votes
0answers
81 views

Saving String as unique number in Mongo

I have a field in my collection which is a String in nature. When i run aggregation for this field in data it takes considerable time in aggregation. Total 6 million records are in collection which ...