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 &...
- 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
,...
- 152k
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 ...
- 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 ...
- 152k
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 ...
- 71.2k
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, ...
- 152k
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 ...
- 92.9k
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 ...
- 152k
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(*).
- 92.9k
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 ...
- 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, ...
- 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....
- 152k
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, ...
- 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 ...
- 65.8k
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 =...
- 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()...
- 71.2k
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 ...
- 20k
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 ...
- 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 ...
- 152k
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 ...
- 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 ...
- 152k
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 ...
- 10.1k
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
...
- 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 ...
- 152k
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 ...
- 152k
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
...
- 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; -- ...
- 152k
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 ...
- 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 ...
- 173k
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
Only top scored, non community-wiki answers of a minimum length are eligible