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 &...
user avatar
  • 33.5k
32 votes
Accepted

Rolling sum / count / average over date interval

The query you have You could simplify your query using a WINDOW clause, but that's just shortening the syntax, not changing the query plan. SELECT id, trans_ref_no, amount, trans_date, entity_id ,...
user avatar
31 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 ...
user avatar
  • 56.2k
31 votes
Accepted

Combining separate ranges into largest possible contiguous ranges

Assumptions / Clarifications No need to differentiate between infinity and open upper bound (upper(range) IS NULL). (You can have it either way, but it's simpler this way.) NULL vs. infinity in ...
user avatar
31 votes
Accepted

Why does COUNT() aggregate return 0 for 'NULL'?

Aggregate functions ignore null values. So SELECT COUNT(cola) AS thecount FROM tablea is equivalent to SELECT count(*) AS thecount FROM tablea WHERE cola IS NOT NULL; As all of your values are ...
user avatar
31 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, ...
user avatar
28 votes
Accepted

Postgres error [column must appear in the GROUP BY clause or be used in an aggregate function] when sub query is used

The feature of Postgres to be able to use the primary key of a table with GROUP BY and not need to add the other columns of that table in the GROUP BY clause is relatively new and works only for base ...
user avatar
25 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 ...
user avatar
20 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(*).
user avatar
19 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 ...
user avatar
  • 56.2k
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, ...
user avatar
  • 70.8k
17 votes
Accepted

Filling in missing dates in record set from generate_series()

This is some kind of misunderstanding. The query in your question already returns what you are asking for. I only changed minor details: SELECT text 'Inspections' AS data_label , count(i....
user avatar
16 votes
Accepted

Calculate rolling sum over 7 consecutive day period on PostgreSQL

By far the cleanest solution is to use window function sum with rows between: with days as ( SELECT date_trunc('day', d)::date as day FROM generate_series(CURRENT_DATE-31, ...
user avatar
  • 570
16 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 ...
user avatar
16 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 =...
user avatar
  • 78.1k
16 votes
Accepted

Combining array_agg and unnest

You need to do the unnest in a sub-query: select d."key", array_agg(distinct x.v) from data d cross join lateral unnest(d."values") as x(v) group by d."key"; Set returning functions (like unnest()...
user avatar
15 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 ...
user avatar
14 votes
Accepted

Array integer[]: how to get all distinct values in a table and count them?

You can use UNNEST. select unnest(ports) as port, count(*) from foo group by port; Using more than one UNNEST in the same query (or the same select list, anyway) is confusing and is probably best ...
user avatar
  • 32k
14 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 ...
user avatar
13 votes
Accepted

Return counts for multiple ranges in a single SELECT statement

Just use conditional SUM() statements per column for each number range. The total can be summed by just using SUM(1), assuming all of the data in the table is within one of the ranges - if not, just ...
user avatar
  • 30.8k
13 votes

Return counts for multiple ranges in a single SELECT statement

Aggregate FILTER clause in Postgres 9.4+ Since Postgres 9.4 there is a clean and fast (SQL standard) way: SELECT count(*) FILTER (WHERE score BETWEEN 0 AND 3) AS low , count(*) FILTER (WHERE ...
user avatar
13 votes

Calculating Average Excluding Outliers

A very basic example would be to get the AVG and STDEV of the range of numbers and then exclude any that were more than 1 Standard Deviation from that average. You then take the average of the new ...
user avatar
11 votes

Eliminate duplicates in ListAgg (Oracle)

Although this is an old post with an accepted answer, I think the LAG() analytic function works well in this case and is noteworthy: LAG() removes duplicate values in column num2 with minimal expense ...
user avatar
  • 211
11 votes
Accepted

PostgreSQL Division In Query Not Working

Integer division truncates fractional digits. Your expression returns a ratio between 0 and 1, which is always truncated to 0. To get "percentage", first multiply by 100. To also get fractional ...
user avatar
11 votes
Accepted

Postgres Index a query with MAX and groupBy

Too bad If you cannot change the query at all, that's too bad. You won't get a good solution. If you had not table-qualified the table (run.frames_stat), you could create a materialized view (see ...
user avatar
11 votes
Accepted

How can I pass column to function in sql?

There are much more efficient ways to calculate a simple or grouped median than the one shown in your question: What is the fastest way to calculate the median? Best approaches for grouped median ...
user avatar
  • 70.8k
11 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 WHERE time > 0 -- filtered just to take positive values (orig. Q) ORDER BY gid, time_interval; -- ...
user avatar
11 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 ...
user avatar
  • 281
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 ...
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....
user avatar
  • 111

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