aggregate-functions

LISTAGG in Oracle to return distinct values

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

PostgreSQL: running count of rows for a query ‘by minute’

Return only minutes with activity Shortest SELECT DISTINCT date_trunc(‘minute’, “when”) AS minute , count(*) OVER (ORDER BY date_trunc(‘minute’, “when”)) AS running_ct FROM mytable ORDER BY 1; Use date_trunc(), it returns exactly what you need. Don’t include id in the query, since you want to GROUP BY minute slices. count() is typically used as plain aggregate … Read more

Spark SQL: apply aggregate functions to a list of columns

There are multiple ways of applying aggregate functions to multiple columns. GroupedData class provides a number of methods for the most common functions, including count, max, min, mean and sum, which can be used directly as follows: Python: df = sqlContext.createDataFrame( [(1.0, 0.3, 1.0), (1.0, 0.5, 0.0), (-1.0, 0.6, 0.5), (-1.0, 5.6, 0.2)], (“col1”, “col2”, … Read more

Does T-SQL have an aggregate function to concatenate strings? [duplicate]

for SQL Server 2017 and up use: STRING_AGG() set nocount on; declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5)) insert into @YourTable VALUES (1,1,’CCC’) insert into @YourTable VALUES (2,2,’B<&>B’) insert into @YourTable VALUES (3,2,’AAA’) insert into @YourTable VALUES (4,3,'<br>’) insert into @YourTable VALUES (5,3,’A & Z’) set nocount off SELECT t1.HeaderValue ,STUFF( (SELECT ‘, … Read more

Optimal way to concatenate/aggregate strings

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

SELECTING with multiple WHERE conditions on same column

You can either use GROUP BY and HAVING COUNT(*) = _: SELECT contact_id FROM your_table WHERE flag IN (‘Volunteer’, ‘Uploaded’, …) GROUP BY contact_id HAVING COUNT(*) = 2 — // must match number in the WHERE flag IN (…) list (assuming contact_id, flag is unique). Or use joins: SELECT T1.contact_id FROM your_table T1 JOIN your_table … Read more