32
You can use regular expressions and regexp_replace to remove the duplicates after concatenation with listagg:
SELECT Num1,
RTRIM(
REGEXP_REPLACE(
(listagg(Num2,'-') WITHIN GROUP (ORDER BY Num2) OVER ()),
'([^-]*)(-\1)+($|-)',
'\1\3'),
'-') Num2s
FROM ListAggTest;
This could be tidier if Oracle's ...
25
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 ...
25
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',...
25
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
48.7k9 gold badges97 silver badges128 bronze badges
22
I tested the performance of all 3 methods, and here's what I found:
1 record: No noticeable difference
10 records: No noticeable difference
1,000 records: No noticeable difference
10,000 records: UNION subquery was a little slower. The CASE WHEN query is a little faster than the UNPIVOT one.
100,000 records: UNION subquery is significantly slower, but ...
22
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 ...
21
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.
Per documentation:
The built-in range types int4range, int8range, and ...
20
I'm afraid that the reason is simply that the rules were set in an adhoc fashion (like quite many other "features" of the ISO SQL standard) at a time when SQL aggregations and their connection with mathematics were less understood than they are now (*).
It's just one of the extremely many inconsistencies in the SQL language. They make the language harder ...
20
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 ...
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
answered Jun 20 at 5:32
18
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(*).
17
Per the standard:
SELECT 1 FROM r HAVING 1=1
means
SELECT 1 FROM r GROUP BY () HAVING 1=1
Citation ISO/IEC 9075-2:2011 7.10 Syntax Rule 1 (Part of the definition of the HAVING clause):
Let HC be the <having clause>. Let TE be the <table expression> that
immediately contains HC. If TE does not immediately contain a
<group by clause&...
17
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 "...
17
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 ...
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
I am not familiar with accounting, but I solved some similar problems in inventory-type environments. I store running totals in the same row with the transaction. I am using constraints, so that my data is never wrong even under high concurrency. I have written the following solution back then in 2009::
Calculating running totals is notoriously slow, ...
14
Not allowing customers to have a less than 0 balance is a business rule (which would change quickly as fees for things like over draft are how banks make most of their money). You'll want to handle this in the application processing when rows are inserted into the transaction history. Especially as you may end up with some customers having overdraft ...
13
If you want to show 0 (alas 1 row) when your query returns 0 rows, then you could use:
SELECT COALESCE(
( SELECT MAX(post_id) FROM my_table WHERE org_id = 3 )
, 0) AS max_id
13
A slightly different approach (similar to your 2nd option) to consider is to have just the transaction table, with a definition of:
CREATE TABLE Transaction (
UserID INT
, CurrencyID INT
, TransactionDate DATETIME
, OpeningBalance MONEY
, TransactionAmount MONEY
);
You may also want a transaction ID/...
13
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 ...
12
After reading these two discussions, I decided on option 2
Having read those discussions too, I am not sure why you decided on the DRI solution over the most sensible of the other options you outline:
Apply transactions to both the transactions and balances tables.
Use TRANSACTION logic in my stored procedure layer to ensure that
balances and ...
12
You are not showing the query you are using to obtain the results without diff. I'm assuming it is something like this:
SELECT
min = MIN(Value),
max = MAX(Value),
avg = AVG(Value), -- or, if Value is an int, like this, perhaps:
-- AVG(CAST(Value AS decimal(10,2))
Date = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
FROM atable
...
11
This is some kind of misunderstanding. The query in your question already returns what you are asking for. I only changed minor details:
SELECT 'Inspections'::text AS data_label
, count(i.reporting_id) 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
107k11 gold badges229 silver badges358 bronze badges
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
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.
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 =...
answered Jan 26 '16 at 7:17
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
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:
As with hash join, the hash aggregate requires memory. Before executing a query with a hash aggregate, SQL Server uses cardinality estimates to estimate ...
answered Jul 17 at 20:47
10
You can calculate this in one step with window functions:
CREATE OR REPLACE VIEW daily_trans AS
SELECT DISTINCT
trans_date
, first_value(trans_time) OVER w AS first_time
, first_value(id) OVER w AS first_id
, last_value(trans_time) OVER w AS last_time
, last_value(id) OVER w AS last_id
, calculate_status(min(...
answered Dec 27 '11 at 19:50
Erwin Brandstetter
107k11 gold badges229 silver badges358 bronze badges
10
I must first compliment you on your courage to do something like this with an Access DB, which from my experience is very difficult to do anything SQL-like. Anyways, on to the review.
First join
Your IIF field selections might benefit from using a Switch statement instead. It seems to be sometimes the case, especially with things SQL, that a SWITCH (more ...
Only top voted, non community-wiki answers of a minimum length are eligible
