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
56 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
55 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 ...
1
vote
1answer
34 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
53 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 ...
0
votes
2answers
43 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
votes
1answer
64 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 = ...
0
votes
1answer
27 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
6k 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 ...
1
vote
1answer
63 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 | ...
0
votes
1answer
153 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 ...
2
votes
1answer
44 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
18 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
132 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
56 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
103 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
33 views

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

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
33 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
0answers
25 views

Order by Like Value and Count

My Name field values are "Name1 (AB)", "Name2 (AB)", "Name3 (CD)", "Name4 (EF)", etc. Is it possible to count the total "%(AB)" and the total "%(CD)" etc...in one query or will it require a sub query? ...
1
vote
1answer
40 views

Fetch data from same table using two group by clauses in mysql

I have one table name as consultant with 4 fields(id, name,user_type, Created date). I need to write an mysql query for return the count of user_type in each month. In my table, user_type is ...
0
votes
1answer
57 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 ...
1
vote
2answers
47 views

Query for frequency of e-mail addresses in a field

Newbie here. I know the answer is out there, but lack the vocabulary to ask the right question... I am looking for a MYSQL query which gives me the frequency of addresses in the field emailaddress. ...
4
votes
2answers
1k 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 ...
0
votes
2answers
62 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) ...
1
vote
2answers
77 views

Aggregate function / query in Oracle 11g

I am working in Oracle 11g. I have been racking my brain all day trying to figure out how to write a particular SQL statement (am a Java/.NET Developer, would not consider myself a SQL expert) I ...
1
vote
1answer
63 views

Split two rows into two columns

I have the following table: id | name | action | count ------------------------------ 1 | Value1 | 0 | 27 1 | Value1 | 1 | 49 2 | Value2 | 0 | 7 2 | Value2 | 1 | ...
1
vote
2answers
33 views

Aggregating on a repeated group

In a current report, I'm trying to track the migration of a person from department n, to n+1, or to n+k. My data is split into a row per term (university data). An example: dept | term ...
1
vote
3answers
2k views

Select query to retrieve values even if are null

I need some help in a query. Need to retrieve data from 1 table, even if one of the field has null value. Here's an example: Select name, SUM(credit) as credit From expenses Where name like ...
2
votes
1answer
120 views

Select data based on records created_at column

I have two tables Keywords, ProjectReports: http://sqlfiddle.com/#!15/06ae3/1 As you can see in that example everything works fine, but I want to get more data from those 2 tables and I don't know ...
0
votes
0answers
29 views

Mongo - Fetch results sorted by the number of matching values in an array object

I have a mongo Database with collections such as : [{ _id : 'one', tags1 : ['A', 'B', 'C'], tags2 : ['D', 'E', 'F'] },{ _id : 'two', tags1 : ['A', 'Z'], tags2 : ['D', 'F', 'G', 'M'] } ...
1
vote
0answers
55 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
132 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
207 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 ...
3
votes
0answers
176 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
28 views

Is it possible to find the user whose aggregated score reaches X value first?

Given this sample fiddle containing sales value and date for two users: http://sqlfiddle.com/#!2/8148be/3 How can I find the first user who has reached a total saleValue of 30 since the beginning of ...
2
votes
2answers
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
2answers
37 views

Aggregating results of two database views in one database view

I have a view with name of UserInfo like this: And another view with name of UserRoleInfo like this: I want to aggregate all the records of the two views for one user name like H.F into ...
2
votes
2answers
248 views

Returning empty string when string_agg has no records

I am trying to return a text field in a PostgreSQL query that is of the form 'stringOne' || string_agg(field, ',') || 'stringTwo' where for certain elements in the group clause, field is always ...
1
vote
1answer
262 views

Select query for returning different data based on creation time

I have a view, which will Select data from table reports the select will filter the data based on multiple dates. reports table: id, key_id, position, created_at My select: pr.key_id, ...
0
votes
1answer
115 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 | ...
1
vote
1answer
53 views

Consolidating large table

I need some help with a complex query. Table def: Table "public.agent_log" Column | Type | Modifiers ...
1
vote
1answer
102 views

Retrieve title from rows with a maximum count in other columns

Given the following table archived: CREATE TABLE archived ( sid serial -- row id showurl text -- can be: btg, tmwwt, gcaf, emda title text reference text -- title id ...
0
votes
2answers
74 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 ...
1
vote
2answers
5k 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 | ...
2
votes
1answer
202 views

How can I use running total aggregates in a query to output financial accumulations?

I am attempting to write a query that will be consumed by a SSRS 2012 report. Given a paid invoice, with a certain set of account allocations towards which the money paid towards it must be saved, I ...
1
vote
0answers
108 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 ...
5
votes
4answers
381 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
1answer
615 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
1answer
133 views

Slow queries related to subqueries using aggregation

Here's a simplified version of the queries I am running: select ... from editions join edition_events on edition_events.edition_id = editions.id join events on events.id = ...
0
votes
1answer
59 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 ...
17
votes
10answers
34k views

Eliminate duplicates in ListAgg (Oracle)

Prior to Oracle 11.2 I was using a custom aggregate function to concatenate a column into a row. 11.2 Added the LISTAGG function, so I am trying to use that instead. My problem is that I need to ...