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
1answer
18 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
13 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 ...
1
vote
2answers
45 views

Return counts for multiple ranges in a single SELECT statement

I have a Postgres database table foo that, among other things, has a column for score that ranges from 0 - 10. I want a query to return the total number of scores, the number of scores between 0 and ...
1
vote
2answers
49 views

Postgres Index a query with MAX and groupBy

Is there any way to index the following query? SELECT run_id, MAX ( frame ) , MAX ( time ) FROM run.frames_stat GROUP BY run_id; I've tried creating sorted (non-composite) indexes on frame and ...
-1
votes
1answer
65 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 ...
1
vote
2answers
683 views

LIMIT to one row per ID

This query gives me multiple hits because of each assetid may contain many vb.title. How can I limit the result to only 1 hit for each assetid? 'select DISTINCT v.id, v.purchasedate, ...
1
vote
1answer
40 views

Join and count boolean columns by logical OR

There are two tables: user_table and user_schedule. Each user has four schedule rows (one for each 6 hour period per day) with one column for every workday. The desired result is to count the number ...
0
votes
0answers
19 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 ...
0
votes
1answer
21 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 ...
1
vote
1answer
85 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
293 views

Calculating a running balance to store with transactions

I need to put a read-only copy of balance and transaction data from a legacy system, which will only be used to view/print statements. Balance | id | member_id | product_id | balance | | 1 | ...
1
vote
2answers
61 views

Calculating percentage from two tables

I have two tables: First one contains unique mentees under one mentor, so mentors can have more than one mentee in that table. Second one contains interactions of mentors with mentees on different ...
0
votes
0answers
19 views

index of aggregate value with fast update?

Is it possible for me to index the sum of a particular table column, such that future lookups will not have to scan the entire table? This table will grow to be very large and a sum of the entire ...
0
votes
1answer
284 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
3answers
91 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, ...
1
vote
1answer
46 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
1answer
408 views

Postgres error [column must appear in the GROUP BY clause or be used in an aggregate function] when sub query is used

I have two tables employee and phones. An employee can have 0 to n phone numbers. I want to list the employee names with their phone numbers. I am using the below query which runs fine. SELECT ...
1
vote
2answers
88 views

Understanding multiple table join with aggregation

I have a basic question on how JOIN works on multiple tables. I want to count occurrences of Foreign Key in link1 & link2 CREATE TABLE main ( id SERIAL PRIMARY KEY, name text NOT NULL ); ...
-1
votes
1answer
194 views

Sum of two columns from pivot

I have the following query: DECLARE @columns NVARCHAR(MAX),@columns1 NVARCHAR(MAX), @sql NVARCHAR(MAX),@columns2 NVARCHAR(MAX),@columns3 NVARCHAR(MAX); SET @columns = '1,2,3,4'; SELECT @columns1 = ...
3
votes
2answers
50 views

Selecting minimum value in top grouping only

I have a query built in Oracle 10 which produces a set of data like this: EMP_ID WORK_DT TIME_TYPE ---------- --------- --------- 12345 19-DEC-14 10 12345 18-DEC-14 10 ...
0
votes
2answers
43 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 ...
3
votes
3answers
2k views

How do I create a user-defined aggregate function?

I need an aggregate function that MySQL doesn't provide. I would like it to be in MySQL's flavor of SQL (that is, not in C). How do I do this? What I'm stuck on is creating an aggregate function -- ...
0
votes
0answers
37 views

aggregate array records in mongo db

HI All I have this Data in mongo, {"articleId" : [ { "articleId" : "9514666", "articleCount" : 1 } ], "count" : NumberLong(1), ...
2
votes
3answers
172 views

Return total number of rows and selected (aggregated) data

I have a function which is selecting some data from a table. I would like to return the selected data and the total number of rows in that table. How can I do that or how can I get the same result in ...
1
vote
0answers
52 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 ...
4
votes
1answer
428 views

Is there a First() aggregate function in postgresql that returns the exact same type as the input?

Full question re-write I'm looking a First() aggregate function. Here I found something that almost works: CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS anyelement ...
1
vote
1answer
68 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 ...
6
votes
5answers
4k views

Use of HAVING without GROUP BY in SQL queries

In order to use HAVING in SQL queries , must there be a GROUP BY to aggregate the column names? Are there any special cases where it is possible to use HAVING without a GROUP BY in SQL queries? ...
1
vote
3answers
233 views

Select only rows with minimum values from union table

SQL Server 2005 Here is a sampling of the data from a union of two tables (named "vProducts_All"): DBID UPC Price1 Price2 1 023614025559 689.0000 649.0000 2 ...
1
vote
1answer
63 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
2answers
46 views

Multidimensional aggregates

Suppose one wanted to cache simple aggregate data over a table or grouped parts of a table, one could simply make a table with the aggregated results. If the original table is only appended to, it is ...
4
votes
2answers
2k views

PostgreSQL query with max and min date plus associated id per row

I have the following table: CREATE TABLE trans ( id SERIAL PRIMARY KEY, trans_date date, trans_time time ); I want to have the following view CREATE OR REPLACE VIEW daily_trans ...
1
vote
2answers
75 views

Optimizing for data that does not fit in memory

We have a database that contains around 235 GB of data (calculcated using pg_total_relation_size for each table) on a server with around 77GB of memory. The server is used for nothing else (aside from ...
1
vote
1answer
58 views

How group by affects aggregate functions

I tried to understand the following scenarios: Table name: family +------+-------+ | id | name | +------+-------+ | 1 | alice | | 2 | bob | +------+-------+ That table will be used for ...
1
vote
1answer
51 views

Writing a view that caches to another table

I have a table market_trades with columns timestamp, market_id, and amount: api_production=# \d market_trades; Table "public.market_trades" Column | ...
0
votes
1answer
40 views

SQL - Add column if match else, on aggregate data

I have an order table that roughly looks like this |Customer id |Date | revGBP |orderID |GiftOrder | --------------------------------------------------------------------- |1 ...
1
vote
0answers
62 views

ORA-00937 when calculating a sub-value in an aggregate select statement

The problem described below applies to Oracle 11g databases. I also tested the statements on MySQL and PostgreSQL with no problems of the kind mentioned in this question. I want to use an ...
1
vote
3answers
105 views

Longest matching suffix while using aggregate functions

Background Looking to find the longest matching string suffix. Setup Consider the following fiddle: CREATE TABLE noun ("label" varchar(10)) ; INSERT INTO noun ("label") VALUES ...
1
vote
3answers
81 views

Can I perform a MAX over a pair of columns?

I would like to execute the following: SELECT person, MAX( (date, priority) ) FROM table GROUP BY person; It would return a person, date and priority row for each distinct value in the person ...
0
votes
1answer
103 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 ...
-2
votes
1answer
128 views

How do I get to this query? SQL Server

I have a database schema : member(memb_no, name, age) book(isbn, title, authors, publisher) borrowed(memb_no, isbn, date) This is the question: For each publisher, print the names of ...
0
votes
2answers
74 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
81 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 | ...
3
votes
2answers
8k views

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

Lets say I have a table of employees, like this: Name Sex Role Bob M Developer Joe M QA Now, I have a problem with this table of duplicated rows. I will fix it ...
2
votes
1answer
163 views

PostgreSQL Division In Query Not Working

So I am having a really off day or something, but for the life of me I can not figure this out. I am trying to divide tier1 by total to get a percentage of the total. I thought like this: (tier1 / ...
0
votes
2answers
56 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 ...
2
votes
3answers
1k views

Why MAX() of SUM() giving wrong output?

I have a table employee id name salary city 1 ram 50000 c1 2 sham 20000 c2 3 jadu 80000 c1 4 madhu 90000 c4 5 hari 10000 c2 6 gopal 34000 c3 7 ...
2
votes
3answers
186 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 ...
1
vote
0answers
128 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 ...
0
votes
1answer
100 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 ...