45
votes
Accepted
In PostgreSQL, is there a type-safe first() aggregate function?
DISTINCT ON()
Just as a side note, this is precisely what DISTINCT ON() does (not to be confused with DISTINCT)
SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of ...
- 64.2k
38
votes
Accepted
Convert right side of join of many to many into array
To aggregate most rows
While querying all or most items, it is typically substantially faster to aggregate rows from the "many"-table first and join later:
SELECT id, i.title AS item_title, ...
- 180k
37
votes
Accepted
SQL Server returns "Arithmetic overflow error converting expression to data type int."
For values larger than the INT max (2,147,483,647), you'll want to use COUNT_BIG(*).
SELECT COUNT_BIG(*) AS [Records], SUM(t.Amount) AS [Total]
FROM dbo.t1 AS t
WHERE t.Id > 0
AND t.Id &...
30
votes
Accepted
Why is array_agg() slower than the non-aggregate ARRAY() constructor?
There is nothing "old school" or "outdated" about an ARRAY constructor (That's what ARRAY(SELECT x FROM foobar) is). It's modern as ever. Use it for simple array aggregation.
The manual:
It is ...
- 180k
21
votes
Why does COUNT() aggregate return 0 for 'NULL'?
This is by design.
COUNT(<expression>) counts rows where the <expression> is not null.
COUNT(*) counts rows.
So, if you want to count rows, use COUNT(*).
- 98.5k
21
votes
Convert right side of join of many to many into array
You need to add the group by clause and use array_agg.
SELECT i.id, i.title, array_agg(i.title)
FROM items i
INNER JOIN items_tags it
ON it.item_id = i.id
INNER JOIN tags t
ON t.id = it.tag_id
GROUP ...
- 64.2k
19
votes
Accepted
Why is this stream aggregate necessary?
You can see the role of this aggregate if no rows match the WHERE clause.
SELECT MAX(Revision)
FROM dbo.TheOneders
WHERE Id = 1
AND 1 = 1 /*To avoid auto parameterisation*/
AND Id%3 =...
- 85.9k
19
votes
Accepted
Can we put an equal sign (=) after aggregate functions in Transact-SQL?
This is documented in UPDATE (Transact-SQL):
SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, ...
- 88.3k
17
votes
Accepted
WHERE vs. HAVING on non-aggregate columns. Advantages / Disadvantages / Irrelevant?
The conditions in HAVING are not applied against the aggregations, but on the non-aggregated columns.
The problem here is in how you are describing what the HAVING clause applies to. The HAVING ...
- 69.4k
16
votes
In PostgreSQL, is there a type-safe first() aggregate function?
Yay, I've found out an easy way with your case by using some features in PostgreSQL 9.4+
Let's see this example:
select (array_agg(val ORDER BY i))[1] as first_value_orderby_i,
(array_agg(val ...
- 259
16
votes
Accepted
SUM over distinct rows with multiple joins
Since there can be multiple payments and multiple extras per item, you run into a "proxy cross join" between those two tables. Aggregate rows per item_id before joining to item and it should ...
- 180k
16
votes
Accepted
Why am I getting nondeterministic results with the deterministic function STDEV()?
row_number is not deterministic if there can be ties (i.e. rows with the same PartitionField and DateField values). Any of the tied values might end up with a PartitionRowId of 1 which would ...
- 20.3k
13
votes
Accepted
Select rows based on a minimum in one column
Use DISTINCT ON:
SELECT DISTINCT ON (gid)
id, gid, distance, time_interval
FROM dis
ORDER BY gid, time_interval; -- more expressions to break ties?
Detailed explanation:
Select first row ...
- 180k
13
votes
Accepted
SQLite - How does Count work without GROUP BY?
SQLite does not adhere to the standard in this regard. See https://www.sqlite.org/quirks.html#aggregate_queries_can_contain_non_aggregate_result_columns_that_are_not_in_the_group_by_clause
SQLite ...
- 23.6k
12
votes
Why is array_agg() slower than the non-aggregate ARRAY() constructor?
I believe the accepted answer by Erwin could be added with the following.
Usually, we are working with regular tables with indices, instead of temporary tables (without indices) as in the original ...
- 291
11
votes
What is the most efficient way to concatenate strings in SQL Server?
If you're on SQL Server 2017+, there's a built-in function that's a bit simpler to use than a subquery with XML and STUFF, etc.
You can use STRING_AGG.
SELECT
p.OwnerUserId,
aggro =
...
11
votes
Accepted
GROUP BY gives wrong result with MIN() aggregate function
As a MySQL DBA, I sadly admit that MySQL can be rather cavalier in its SQL processing. One of the most infamous feats of this is its GROUP BY behavior.
As example, Aaron Bertrand answered the post Why ...
- 184k
11
votes
SQL Server returns "Arithmetic overflow error converting expression to data type int."
This issue is caused by SUM() function
you have to CAST t.Amount as BIGINT
SELECT COUNT(*) AS [Records], SUM(CAST(t.Amount AS BIGINT)) AS [Total]
FROM dbo.t1 AS t
WHERE t.Id > 0
AND t....
- 111
11
votes
Accepted
Set non-negative floor for rolling sum, in PostgreSQL
This is how I solved a similar problem on Teradata using nested OLAP-functions:
SELECT dt.*,
-- find the lowest previous CumSum < 0
-- and adjust the current CumSum to zero
Max(...
- 4,206
11
votes
WHERE vs. HAVING on non-aggregate columns. Advantages / Disadvantages / Irrelevant?
Solomon gives very good explanations, but to me, the easy answer is to remember the SQL query logical processing order as Itzik Ben-Gan wrote here
The sequence is always
FROM -> WHERE -> GROUP BY -> ...
- 3,083
11
votes
Accepted
Hash aggregate bailout
Hash join and hash aggregate both use the same operator code internally, though a hash aggregate uses only a single (build) input. The basic operation of hash aggregate is described by Craig Freedman:
...
- 88.3k
10
votes
Accepted
Get ID of row in aggregate
You can use window (ranking). functions for this:
WITH ct AS
( SELECT X, Y, RowAddedDate, ID,
Rn = DENSE_RANK() OVER (PARTITION BY X, Y
ORDER BY ...
- 98.5k
10
votes
Accepted
Where clause over multiple rows, GROUP BY
The design pattern is called EAV (entity-attribute-value). It's usual to need either GROUP BY or queries with multiple joins with this design:
SELECT
a.AccountID
FROM
tbl ...
- 98.5k
10
votes
Accepted
How to select multiple values into an array and loop through? (postgres 9.3)
A much better way, yet: just update. No loop needed.
UPDATE loop_test
SET id_copy = id
, other_id_copy = other_id;
WHERE id IS NOT NULL;
The WHERE condition is only useful if id can be null ...
- 180k
9
votes
Accepted
How to create aggregate function, like sum, with an implicit DISTINCT?
Is it possible to create an aggregate function (SUM_DISTINCT), that returns the same result as as SUM(DISTINCT foo), so SUM_DISTINCT(foo) = SUM(DISTINCT foo)?
Yes, it is possible — you need a User-...
- 40.1k
9
votes
Accepted
Find Max Value for each month for the last 3 months, properly
For SQL Server, you could do something like this.
DECLARE @t TABLE (Id INT, DateVal DATETIME, ValueVal DECIMAL(18, 9));
INSERT @t ( Id, DateVal, ValueVal )
SELECT Id, DateVal, ValueVal
FROM (
...
9
votes
Accepted
Aggregate ranges by merging
Create an aggregate using the range_merge(anyrange, anyrange) function.
CREATE AGGREGATE range_merge(anyrange)
(
sfunc = range_merge,
stype = anyrange
);
SELECT range_merge(r) FROM test_ranges;
=...
- 253
9
votes
Accepted
Accumulate values into an array with Postgres
If your PostggreSQL version is 9.4 or later use FILTER clause:
select
array_agg(my_table.price) filter(where my_table.property_type = 'FLAT' ) as Flat_Current_Asking_Price,
array_agg(my_table....
- 1,579
Only top scored, non community-wiki answers of a minimum length are eligible