Skip to main content
Share Your Experience: Take the 2024 Developer Survey
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 ...
Evan Carroll's user avatar
  • 63.5k
37 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, ...
Erwin Brandstetter's user avatar
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 &...
Erik Darling's user avatar
  • 41.1k
29 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 ...
Erwin Brandstetter's user avatar
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(*).
ypercubeᵀᴹ's user avatar
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 ...
Evan Carroll's user avatar
  • 63.5k
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 =...
Martin Smith's user avatar
  • 84.8k
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, ...
Paul White's user avatar
  • 85.8k
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 ...
Solomon Rutzky's user avatar
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 ...
Mabu Kloesen's user avatar
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 ...
Erwin Brandstetter's user avatar
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 ...
Justin Cave's user avatar
  • 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 ...
Erwin Brandstetter's user avatar
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 ...
Lennart - Slava Ukraini's user avatar
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 ...
pbillen's user avatar
  • 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 = ...
Erik Darling's user avatar
  • 41.1k
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 ...
RolandoMySQLDBA's user avatar
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....
m.geagea's user avatar
  • 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(...
dnoeth's user avatar
  • 4,196
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 -> ...
jyao's user avatar
  • 3,063
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: ...
Paul White's user avatar
  • 85.8k
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 ...
ypercubeᵀᴹ's user avatar
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 ...
ypercubeᵀᴹ's user avatar
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 ...
Erwin Brandstetter's user avatar
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-...
Jack Douglas's user avatar
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 ( ...
Erik Darling's user avatar
  • 41.1k
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; =...
Victor's user avatar
  • 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....
Abelisto's user avatar
  • 1,559

Only top scored, non community-wiki answers of a minimum length are eligible