Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.
Search type Search syntax
Tags [tag]
Exact "words here"
Author user:1234
user:me (yours)
Score score:3 (3+)
score:0 (none)
Answers answers:3 (3+)
answers:0 (none)
isaccepted:yes
hasaccepted:no
inquestion:1234
Views views:250
Sections title:apples
body:"apples oranges"
URL url:"*.example.com"
Favorites infavorites:mine
infavorites:1234
Status closed:yes
duplicate:no
migrated:no
wiki:no
Types is:question
is:answer
Exclude -[tag]
-apples
For more details on advanced search visit our help page
Results tagged with Search options answers only user 3684

GROUP BY : SQL Aggregation for Common Values

2
votes
You can simplify further: SELECT date_trunc('month', c.created_at) AS year_month , p.reference, , count (distinct c.id) as nb_crea, , (sum(o.amount_ati_cents) / 100) as ca_euros from …
answered Mar 7 '15 by Erwin Brandstetter
5
votes
1. CROSS JOIN, LEFT JOIN LATERAL to subquery SELECT a.user_id, COALESCE(b.balance, 0) AS balance, d.as_of_date FROM ( SELECT d::date AS as_of_date -- cast to date right away FROM generate_ …
answered Mar 26 '16 by Erwin Brandstetter
4
votes
In addition to what Colin already cleared up: SELECT * FROM crosstab( $$SELECT (applied_date AT TIME ZONE 'America/New_York')::date AS d ,applied_class ,sum(count(id)) OVER (PA …
answered Oct 8 '13 by Erwin Brandstetter
1
vote
A textbook case for window functions. Building on a few assumptions, it could look like this: SELECT grp ,count(*) AS ct ,sum(total) AS total ,min(invoice_number) AS initial_correla …
answered Jul 5 '14 by Erwin Brandstetter
3
votes
First off, LIMIT / OFFSET without ORDER BY are of limited usefulness, since the order is arbitrary and can change any time (when VACUUM runs or when the table is manipulated in at any way). It is only …
answered Jul 22 '13 by Erwin Brandstetter
10
votes
A simple and fast variant: SELECT min(number) AS first_number, count(*) AS ct_free FROM ( SELECT *, number - row_number() OVER (PARTITION BY id_set ORDER BY number) AS grp FROM tbl WHER …
answered Mar 18 '13 by Erwin Brandstetter
1
vote
In Postgres (tested with v9.3) you can use the dedicated inet data type, to store IPv4 addresses with only 7 bytes (or IPv6 with 19 bytes) and with automatic integrity checks and dedicated functions a …
answered May 4 '15 by Erwin Brandstetter
1
vote
You provided crucial information in a later comment: It is a very simple process that will alert when there was no data for one hour of the day or that the clicks or installs were 0 This allows …
answered Mar 22 by Erwin Brandstetter
4
votes
Since you need a subquery in either case, I would use a plain aggregate in the subquery (may be cheaper): SELECT count(*) FROM (SELECT race_id, max(rating100) AS rating100 FROM horse_main …
answered May 21 '16 by Erwin Brandstetter
49
votes
Unfortunately, there is no provision in SQL syntax to say "all columns except this one column". You can achieve your goal by spelling out the remaining list of columns in a row-type expression: SELEC …
answered Jul 3 '14 by Erwin Brandstetter
2
votes
Use RIGHT JOIN instead of INNER JOIN right after your comment in the code: Limit table_A to only records with data from transactions_with_children, but I also need to include a null row for al …
answered May 26 '17 by Erwin Brandstetter
6
votes
What @Joshua displays for MySQL, works in Postgres as well. Boolean values can be cast to integer and added up. The cast needs to be explicit, though. Makes for very short code: SELECT player_id, te …
answered Jun 5 '14 by Erwin Brandstetter
6
votes
The data type of the column people is json, as is the result of json_array_elements(people). And there is no equality operator (=) for the data type json. So you also cannot run GROUP BY on it. More: …
answered Jun 4 '18 by Erwin Brandstetter
17
votes
The LEFT JOIN in @dezso's answer should be good. An index, however, will hardly be useful (per se), because the query has to read the whole table anyway - the exception being index-only scans in Postg …
answered Nov 30 '12 by Erwin Brandstetter
9
votes
First off, your PRIMARY KEY spanning two varchar(2000) columns seems extremely expensive. If you use your PK for anything else I suggest a surrogate PK (use a serial column) and add a UNIQUE constrain …
answered Mar 1 '13 by Erwin Brandstetter

15 30 50 per page