New answers tagged

2

This is standard SQL behaviour for pretty much all databases. There are three ways how rows are returned: without aggregate functions, the query returns one row per row; with aggregate functions, but without grouping, the query returns exactly one row; with aggregate functions and grouping, the query returns one row for each group (i.e., for each unique ...


0

This is expected behaviour (not only for Postgres). If we look at the logical order of evaluation for a query: FROM WHERE GROUP BY SELECT I'll add a table (T) with one column (x) and one row in the example We have: SELECT COUNT(1) as CNT FROM ( VALUES (1) ) AS t(x) WHERE false GROUP BY () -- empty set default The result is one row with value 0. Then ...


0

It would be better to use FILTER (WHERE ...) and COALESCE(..., '{}'::JSON). select json_build_object( 'ID', s.page_id, 'Domain', domain, 'Sections', COALESCE(json_object_agg ( s.title, json_build_object( 'ID', s.sid, 'Texts', t.txts, 'Images', i.imgs ...


0

A late review for an interview: SELECT TOP 1 Count(InvoiceID) Most , VendorState FROM Vendors V JOIN Invoices I ON V.VendorID=I.VendorID GROUP BY VendorState ORDER BY Most DESC It ran with the right answer. Thanks all for your input. A similar one: The state with the most vendors: SELECT TOP 1 Count(VendorState) MostV , VendorState FROM Vendors V JOIN ...


0

I think I've now got a working solution. The below is giving the output as expected. It could probably be optimized a bit as it isn't the quickest (takes a couple minutes for only two companies). In my real dataset I have 65~ companies so if this could be improved, I'd love to see it! SELECT x.yyyy_mm_dd, x.id, x.name, x.actual, x.target,...


1

There are continuous debates within Postgresql about when to return null and when to not return NULL from aggregate function or with any function within prostgresql and this debate rages on in other DBs as well.. In your test case Count(*) the where class returns a empty set aka a NULL so count would return 1 as it had 1 empty/NULL set to count Array_AGG() ...


1

I can't speak from any experience but SQL Server, but I'll assume that this applies to most/all SQL language implementations: COUNT(*) is counting rows. If there are zero rows to count, zero is the response. Most other aggregate functions depend upon values contained in fields (MIN, MAX, SUM, etc). If there are no values to aggregate (either because no ...


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. ...


4

SELECT t1.code_1, SUM(t2.measure_1) FROM table1 t1 NATURAL JOIN table2 t2 GROUP BY t1.code_1 A working example can be found at db<>fiddle where I used the PostgreSQL 10 engine. You can build on this example. Instead of using a static table, you might want to consider using a view instead. This has the advantage that the values will be calculated on-the-...


Top 50 recent answers are included