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.
0
votes
0answers
41 views
How to improve the performance for SQL Server Count(distinct()) and Sum() functions? [closed]
The below one is my query. It's taking 12 seconds for the execution process. The count(distinct()) and SUM() functions are taking long time for execution. I tried it after create the non-cluster index ...
1
vote
1answer
37 views
Mysql: Schema/Query Performance Approach for aggregated mailbox folders
I am about to code a messaging system where users can write messages to other users. User can create custom inbox folders for sorting the messages they receive, however, every user has 2 main inboxes: ...
0
votes
1answer
65 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 ...
1
vote
0answers
131 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 ...
3
votes
1answer
85 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 ...
7
votes
1answer
92 views
Trouble with SQL and aggregates
With this query:
SELECT SUM(qty) AS sumQty,
cnetprodid
FROM quoteitem
WHERE ordered = 1
AND sageSOPOrderReturnLineID IS NOT NULL
AND LEN(LTRIM(RTRIM(cnetprodid))) > 0
...
11
votes
3answers
264 views
What is the correct result for this query?
I came across this puzzle in the comments here
CREATE TABLE r (b INT);
SELECT 1 FROM r HAVING 1=1;
SQL Server and PostgreSQL return 1 row.
MySQL and Oracle return zero rows.
Which is correct? ...
1
vote
1answer
51 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 ...
1
vote
1answer
55 views
How to aggregate datapoints in a table?
Suppose I have following table -
CREATE TABLE data_points (t DATETIME PRIMARY KEY, value INTEGER);
I want to aggregate the data by calculating average of every 10 points in the table.
i.e. If ...
5
votes
1answer
286 views
Oracle aggregate functions slows query down massively
I have a table with ~12M rows of data in it. Here is the table structure:
SYSTEM_ID
BATCH_ID
MEASUREMENT_INDEX,
PARAMETER_ONE,
PARAMETER_TWO
The primary key is made up of the first three columns. I ...
1
vote
2answers
863 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 | ...
4
votes
3answers
174 views
Many-to-Too-Many Relationship
I'm working on a book database. And among others, I have the following two tables (a bit simplified for the question):
tblBook: ID, Title
tblPerson: ID, Name
Note that the tblPerson table contains ...
3
votes
1answer
143 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 ...
4
votes
1answer
91 views
GROUP BY on a dependent field
Let's say I have a SELECT statement as follows:
SELECT ..., field1
FROM ...
GROUP BY field1
Now let's say I want to return a second field, field2. As it happens, field2 is functionally dependent ...
0
votes
2answers
76 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 ...
1
vote
1answer
215 views
ORDER BY on aggregate field on a closure table giving unexpected results
I am updating my companies product database and I'm looking into different ways to do design our faceted navigation "selector" tables. The idea of faceted navigation might look like this:
It's ...
12
votes
3answers
775 views
Why does ANSI SQL define SUM(no rows) as NULL?
The ANSI SQL standard defines (chapter 6.5, set function specification) the following behaviour for aggregate functions on empty result sets:
COUNT(...) = 0
AVG(...) = NULL
MIN(...) = NULL
MAX(...) = ...
3
votes
2answers
197 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
...
4
votes
1answer
198 views
Is it possible to wrap aggregate functions in Postgres?
Postgres' string_agg(expr, delimiter) function is great. But I would like to have a version that takes a single argument -- the field to aggregate -- and assumes a delimiter of ', ' since that is ...
14
votes
5answers
3k views
What is the most efficient way to get the minimum of multiple columns on SQL Server 2005?
I'm in a situation where I want to get the minimum value from of 6 columns.
I've found three ways so far to accomplish this, but I have concerns with the performance of these methods and would like ...
2
votes
1answer
123 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 * ...
1
vote
2answers
414 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 -- ...
9
votes
3answers
2k views
Why is an aggregate query significantly faster with a GROUP BY clause than without one?
I'm just curious why an aggregate query runs so much faster with a GROUP BY clause than without one.
For example, this query takes almost 10 seconds to run
SELECT MIN(CreatedDate)
FROM MyTable
WHERE ...
2
votes
1answer
347 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
218 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
2answers
450 views
postgresql view with max min with id
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
0answers
42 views
postgresql view [duplicate]
Possible Duplicate:
postgresql view with max min with id
I have the following table:
CREATE TABLE trans (
id SERIAL PRIMARY KEY,
trans_date date,
trans_time time
);
I ...
5
votes
4answers
254 views
Find the first gap in an aggregation of integers in SQL Server
Let's say I have a table called dbo.GroupAssignment.
GroupID | Rank
------------------
1 1
1 2
1 3
2 1
2 3
2 4
3 2
3 4
3 5
The PK is GroupID, Rank. Normally, the ranks ...
4
votes
2answers
194 views
MySQL aggregate anomaly
I was checking selectivity of some columns for an index.
Where is this "ignore what I give you" behaviour documented?
This gives 4,851,908, 4,841,060, and 1,000,052
SELECT
COUNT(*),
...
19
votes
7answers
4k views
Writing a simple bank schema: How should I keep my balances in sync with their transaction history?
I am writing the schema for a simple bank database. Here are the basic specifications:
The database will store transactions against a user and currency.
Every user has one balance per currency, so ...
2
votes
3answers
206 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 ...
3
votes
2answers
3k 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 ...
4
votes
2answers
399 views
Is it a good idea to split MySQL DB into two servers
I have a MySQL DB server that accepts a lot of mobile application data for analysis. I would like to know if for optimization reasons the following architecture makes sense and is appropriate:
I ...
5
votes
2answers
630 views
Using totals on aggregates to improve performance
I have two tables: details and totals of these details.
Details (Slow solution):
select
OrderId = r.OrderId
, TotalQty = SUM(r.Quantity)
, ...
4
votes
5answers
6k views
How can I use a default value in a Select query in PostgreSQL?
I would like to use a default value for a column that should be used if no rows is returned. Is that possible in PostgreSQL? How can I do it? Or is there any other way I can solve this?
E.g. ...
6
votes
2answers
487 views
Most efficient way to return multiple aggregates in a single stored proc?
What is the best or most efficient way to get multiple aggregate result values?
Basically I have an email app and wanted to get all the numbers of messages for each type of folder (inbox, sent, ...
35
votes
4answers
5k views
What is the difference between select count(*) and select count(any_non_null_column)?
I seem to remember that (on Oracle) there is a difference between uttering select count(*) from any_table and select count(any_non_null_column) from any_table.
What are the differences between these ...
4
votes
2answers
239 views
MAX for each subset
I have a table that looks like this:
+---------------------------------------------+
| EVENT_ID | ITEM_ID | PERSON_ID | EVENT_DATE |
+---------------------------------------------+
| 123 | 1 ...
4
votes
1answer
935 views
How do I horizontally partition an oracle database table, and should I?
We have a tenanted data warehouse that we are doing reporting on. Queries are beginning to take a long time, and we're looking at options to reduce this. There are two thoughts at the moment.
Create ...
13
votes
9answers
15k 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 ...
6
votes
3answers
4k views
Calculating percentage of a row over total sum
Apologies for the bad title, I wasn't sure what would be a good title for this.
This is currently (simplified view of the) data I'm working with
Agent | Commission
---------|------------
...
37
votes
4answers
2k views
Storing vs calculating aggregate values
Are there any guidelines or rules of thumb to determine when to store aggregate values and when to calculate them on the fly?
For example, suppose I have widgets which users can rate (see schema ...
