Tagged Questions
1
vote
1answer
36 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
59 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
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 ...
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
50 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 ...
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 ...
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 ...
12
votes
3answers
770 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(...) = ...
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 * ...
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 ...
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 ...
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 ...
5
votes
2answers
628 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. ...
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
238 views
MAX for each subset
I have a table that looks like this:
+---------------------------------------------+
| EVENT_ID | ITEM_ID | PERSON_ID | EVENT_DATE |
+---------------------------------------------+
| 123 | 1 ...
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 ...
