That’s how GROUP BY works. It takes several rows and turns them into one row. Because of this, it has to know what to do with all the combined rows where there have different values for some columns (fields). This is why you have two options for every field you want to SELECT : Either … Read more
A simple solution is to wrap the query into a subselect with the ORDER statement first and applying the GROUP BY later: SELECT * FROM ( SELECT `timestamp`, `fromEmail`, `subject` FROM `incomingEmails` ORDER BY `timestamp` DESC ) AS tmp_table GROUP BY LOWER(`fromEmail`) This is similar to using the join but looks much nicer. Using non-aggregate … Read more
19c and later: select listagg(distinct the_column, ‘,’) within group (order by the_column) from the_table 18c and earlier: select listagg(the_column, ‘,’) within group (order by the_column) from ( select distinct the_column from the_table ) t If you need more columns, something like this might be what you are looking for: select col1, listagg(col2, ‘,’) within group … Read more
SOLUTION The definition of optimal can vary, but here’s how to concatenate strings from different rows using regular Transact SQL, which should work fine in Azure. ;WITH Partitioned AS ( SELECT ID, Name, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Name) AS NameNumber, COUNT(*) OVER (PARTITION BY ID) AS NameCount FROM dbo.SourceTable ), Concatenated AS … Read more
You can use NULLIF function e.g. something/NULLIF(column_name,0) If the value of column_name is 0 – result of entire expression will be NULL
You want an outer join for this (and you need to use person as the “driving” table) SELECT person.person_id, COUNT(appointment.person_id) AS “number_of_appointments” FROM person LEFT JOIN appointment ON person.person_id = appointment.person_id GROUP BY person.person_id; The reason why this is working, is that the outer (left) join will return NULL for those persons that do not … Read more
For pandas >= 0.25 The functionality to name returned aggregate columns has been reintroduced in the master branch and is targeted for pandas 0.25. The new syntax is .agg(new_col_name=(‘col_name’, ‘agg_func’). Detailed example from the PR linked above: In [2]: df = pd.DataFrame({‘kind’: [‘cat’, ‘dog’, ‘cat’, ‘dog’], …: ‘height’: [9.1, 6.0, 9.5, 34.0], …: ‘weight’: [7.9, … Read more
Simpler with the aggregate function string_agg() (Postgres 9.0 or later): SELECT movie, string_agg(actor, ‘, ‘) AS actor_list FROM tbl GROUP BY 1; The 1 in GROUP BY 1 is a positional reference and a shortcut for GROUP BY movie in this case. string_agg() expects data type text as input. Other types need to be cast … Read more
You can use GROUP BY SalesOrderID. The difference is, with GROUP BY you can only have the aggregated values for the columns that are not included in GROUP BY. In contrast, using windowed aggregate functions instead of GROUP BY, you can retrieve both aggregated and non-aggregated values. That is, although you are not doing that … Read more
If you’re using SQL 2005 or better this is a nice, simple-ish median calculation for a single column in a table: SELECT ( (SELECT MAX(Score) FROM (SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score) AS BottomHalf) + (SELECT MIN(Score) FROM (SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score DESC) AS TopHalf) … Read more