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)

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 ...
3
votes
2answers
843 views

Pivoting data for stacked charts (grouping and creating new columns for each distinct value)?

I have something that looks like this (SQL Fiddle here): Date Location Value 1/1/2012 Baltimore 36 1/1/2012 Houston 36 1/1/2012 Chicago 55 2/1/2012 Baltimore 49 ...
3
votes
1answer
7k views

Select first row (grouping) + add aggregate function

First have a look at this question on StackOverflow. I'm looking to accomplish the same task, except I also need to add an aggregate function (PostGIS's ST_Union) to my query. How can I combine the ...
3
votes
3answers
132 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 | ...
3
votes
2answers
236 views

Filter and append data to row

My query and my data set (copied from sqlfiddle): CREATE TABLE one ( id int4 primary key, p_id int4, k_id int4 ); CREATE TABLE two( id int4 primary key, p_id int4, k_id int4, ...
3
votes
2answers
3k 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 ...
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
85 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
2answers
85 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 ...
3
votes
1answer
152 views

Aggregating statistics from child rows

I have a parent row and then many child rows, and I must aggregate statistics from these child rows. For a more concrete example let's imagine I have one round of golf (parent row) and then child ...
3
votes
1answer
2k 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 ...
3
votes
1answer
733 views

Is it possible to make a reference to the result of an aggregate function in a SELECT clause from the same SELECT clause?

I'm not a Database Administrator--just a Software Engineer. I would like to know if it is possible to reduce the following T-SQL query: SELECT SUM(Price * Quantity) as 'Total', SUM(Price * ...
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 ...
3
votes
2answers
44 views

Get rows with minimum value

I have tables company: company_id | - - - - - - - - - - - - - - 1 | 2 | employee: employee_id | - - - - - - - - - - - - 1 ...
3
votes
1answer
1k views

Avoid joining to the same table multiple times

I have two tables: CREATE TABLE one ( id int4 primary key, p_id int4, k_id int4, c_id int4 ); CREATE TABLE two( id int4 primary key, p_id int4, k_id int4, t_id int4, pos int4 ); Table ...
3
votes
1answer
934 views

How to represent aggregates in a data warehouse

I am building the dimensional model for a data warehouse (as an exercise for a mini-course I am doing) and I want to build an aggregate to speed up queries. Basically, I want the aggregate to group ...
3
votes
1answer
66 views

Extend a single week's win/loss ratio calculation to a whole season

I'm quite inexperienced at SQL, and I'm trying to improve a query in a small project. The database schema looks somewhat like this. (I've reduced to relevant information, if there seems to be ...
2
votes
4answers
5k views

Should I use the Infobright database or is there a better aggregation solution?

We are a MySQL front-end shop and have a system currently logging event data in a highly structured format. Think apache traffic log. We need to be able to aggregate counts of this data (cube) vis a ...
2
votes
3answers
353 views

Non-deterministic aggregated result

Why is my query non-deterministic? I have query that do the following: select sum(float1*float2*coalesce(float3,1)) from table When I run this query I get a deterministic result, but it seems not ...
2
votes
3answers
8k 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
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 | ...
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 ...
2
votes
1answer
7k 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 ...
2
votes
3answers
3k 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 ...
2
votes
2answers
3k 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 ...
2
votes
3answers
264 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 ...
2
votes
1answer
2k 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 / ...
2
votes
3answers
751 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 ...
2
votes
1answer
84 views

What is the appropriate SQL method for “Safe to SUM”?

In SQL Server 2008 R2+ we, unfortunately, have some nvarchar columns storing numeric data. In some cases we want to SUM the data when it can be summed. The problem is that ISNUMERIC() returns 1 for a ...
2
votes
2answers
269 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 ...
2
votes
3answers
146 views

Aggregate query with min

Having a product_stock table with following structure: product_id warehouse_id stock price Which has bunch of records (same product can be in different warehouses): 1, 1, 0, 500 1, 2, 5, 505 1, 3, ...
2
votes
1answer
341 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 ...
2
votes
1answer
949 views

Why use custom CLR aggregate functions for string concatenation and geometry unions in SQL Server?

I often need to do string concatenation or geometry unions over a column in SQL Server 2008 and I'm aware that you can write custom aggregate functions in .NET and register them with SQL Server to do ...
2
votes
1answer
1k views

Calculate percentage of false records to total records within a aggregate function

I'm looking for a solution to calculate a percentage rate of a boolean value: Model: Products 1 <-> n Components Components.essential is a boolean attribute What I want to calculate ...
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 ...
2
votes
1answer
57 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 ...
2
votes
2answers
179 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 ...
2
votes
1answer
373 views

Why is ORDER BY LIMIT 1 so much faster than MAX?

Here are my two queries: select sts_in from sta_session where sts_user_id=2006 AND sts_sessid!='0jitkt80gg3avere03tqk4lhi6' order by sts_in desc limit 1; And select max(sts_in) from ...
2
votes
1answer
35 views

Search for authors whose records are separated by at least X days

I am using Postgres and have a table which contains metadata about several million images - many of which have the same author. I am looking for contributors who add content regularly - not people ...
2
votes
2answers
77 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
1answer
628 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, ...
2
votes
1answer
794 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 ...
2
votes
1answer
1k views

Is it possible to reduce a group to a row based on some criteria?

I'd like to perform a select on a table involving a GROUP BY such that all rows that share the same set of identifiers are grouped together, but I want to reduce the group to one of the grouped rows ...
2
votes
1answer
501 views

I have multiple sources INSERTing into a MySQL innodb table. My periodic aggregation script never makes accurate aggregates. Why?

I apologize in advance if this is a repeat. I'm not really sure how to properly ask for what I'm running into. I have a large InnoDB table set up. I have 3 sources that all INSERT concurrently at a ...
2
votes
1answer
464 views

Group activities in activity feed by users and products

On my website I'm building an activity feed where I got the basics done and are now making some improvements here and there. One of them is the grouping. I've built it so that if multiple users buys ...
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 ...
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 | ...
2
votes
1answer
107 views

How can I get hours worked totaled for a column?

I get good results until I try to perform calculations on an alias column. I am aware that that is not possible. However I need a breakthrough. Can someone lend a hand please :) I have to finish this ...
2
votes
1answer
109 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 ...
2
votes
1answer
752 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 | ...