36
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 < 101;
If it's happening in the SUM, you need to convert Amount to a BIGINT.
SELECT COUNT(*) AS [Records], SUM(CONVERT(BIGINT, t.Amount)) AS [Total]
FROM ...
32
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
, SUM(amount) OVER w AS trans_total
, COUNT(*) OVER w AS trans_count
FROM transactiondb
WINDOW w AS (PARTITION BY entity_id, date_trunc('month',...
30
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 PostgreSQL range types
Since date is a discrete type, all ranges have default [) bounds.
The manual:
The built-in range types int4range, int8range, and daterange ...
30
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 null, count(cola) has to return zero.
If you want to count the rows that are null, you need count(*)
SELECT cola,
count(*) AS theCount
FROM tablea
...
answered Oct 28 '16 at 9:48
a_horse_with_no_name
66.5k12 gold badges128 silver badges166 bronze badges
29
No.
They don't have to coexist, as proved by the fact that the following query in Oracle works:
select * from dual having 1 = 1;
Similarly, in PostgreSQL the following query works:
select 1 having 1 = 1;
So having doesn't require group by.
Having is applied after the aggregation phase and must be used if you want to filter aggregate results. So the ...
28
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, t.tag_array
FROM items i
JOIN ( -- or LEFT JOIN ?
SELECT it.item_id AS id, array_agg(t.title) AS tag_array
FROM items_tags it
JOIN tags ...
answered May 17 '17 at 22:54
Erwin Brandstetter
140k17 gold badges333 silver badges476 bronze badges
27
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 rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "...
27
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 tables. The optimizer is not (yet?) clever enough to identify primary keys for views, ctes or derived tables (as in your case).
You can add the columns you want ...
22
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 also possible to construct an array from the results of a
subquery. In this form, the array constructor is written with the key
word ARRAY followed by a ...
20
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(*).
19
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, which sets the variable to the pre-update value of the column.
In your code example, sum is the (unwise) name of a column, not an aggregate.
db<>fiddle demo
17
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 BY i.id, i.title,
16
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, CURRENT_DATE-1, '1 day'::interval) d ),
counts as (
select
days.day,
sum((random()*5)::integer) num
FROM days
-- left ...
16
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 = 4 /*always false*/
In that case zero rows go into the aggregate but it still emits one as the correct semantics are to return NULL in this case.
This is a ...
15
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.close_case_date) AS daily_count
, d.day AS date_column
FROM (
SELECT generate_series(timestamp '2013-01-01'
, ...
answered Jul 28 '14 at 21:37
Erwin Brandstetter
140k17 gold badges333 silver badges476 bronze badges
15
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 clause always applies to aggregated fields, which is all remaining columns post-aggregation. You are trying to show / say that the HAVING clause is not being applied ...
15
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 presumably change the final result.
You could use rank instead of row_number but that would cause you to consider all the tied rows which may not be what you want. ...
14
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 avoided.
13
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 restrict it as with the others.
select sum(case when score between 0 and 3 then 1 else 0 end) as minrange,
sum(case when score between 4 and 6 then 1 ...
13
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 score BETWEEN 4 AND 7) AS mid
, count(*) FILTER (WHERE score BETWEEN 8 AND 10) AS high
, count(*) AS total
FROM ...
13
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 range.
This is quite a basic bit of code (don't forget the CAST to a DECIMAL) which you can expand upon to make it more suitable to your needs.
CREATE TABLE #nums (...
12
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 all be correct:
SELECT i.country AS group_by
, COUNT(*) AS item_count
, SUM(i.price) AS cost
, SUM(p.sum_amount) AS earned
...
12
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()) should in general be used in the from part of a query. But to be able to reference a column from the table you need a lateral join.
from data cross join ...
answered Jul 3 '19 at 14:23
a_horse_with_no_name
66.5k12 gold badges128 silver badges166 bronze badges
11
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
No need for non-trivial regular expression to filter results
Just one full table scan (cost=4 on simple example table)
Here is the proposed code:
with nums ...
11
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 digits, cast to numeric (before you divide) - or multiply by 100.0. The presence of a fractional digit in the numeric literal coerces the result to numeric ...
11
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
The general winner for 2012 is a method by Peter Larsson. The pattern is:
Simple Median
SELECT
Median = AVG(1.0 * SQ.YourColumn)
FROM
(
SELECT NumRows =...
11
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 question. It's useful to note that aggregations, such as ARRAY_AGG, cannot leverage existing indices when the sorting is done during the aggregation.
For ...
11
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 do we use Group by 1 and Group by 1,2,3 in SQL query? where he described MySQL's GROUP BY as cowboy who-knows-what-will-happen grouping. I just had to agree.
...
11
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.Id < 101;
Reference
https://stackoverflow.com/questions/8289310/how-to-prevent-arithmetic-overflow-error-when-using-sum-on-int-column
11
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 -> HAVING -> SELECT -> ORDER BY
So you see, if we can have a WHERE filter applied before GROUP BY, we may reduce the amount of data to be processed by GROUP BY, ...
Only top voted, non community-wiki answers of a minimum length are eligible