SQL Aggregate Function to Obtain a List

Sql aggregate function to obtain a list

I used this article once to to the exact same thing:

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Aggregate string values to a list

Based on this SO question, Redshift now has a LISTAGG() analytic function which you can use.

SELECT CUST_ID,
       LISTAGG("ORDER", ', ')
WITHIN GROUP (ORDER BY "ORDER")
OVER (PARTITION BY CUST_ID) AS CUST_ID
FROM Table
ORDER BY CUST_ID

How to find the top 5 values in an aggregate function

You can apply a window function to the result of the aggregate and use that to filter the overall result:

select name, shares
from (
  select c.name,
         SUM(t.shares) as shares,
         dense_rank() over (order by SUM(t.shares) desc) as rnk
  from trade t
    join company c
        on t.stock_id = c.stock_id
    join stock_exchange se
        on se.stock_ex_id = t.stock_ex_id
    join stock_price sp
        on sp.stock_ex_id = se.stock_ex_id 
        and sp.stock_id = c.stock_id
  where se.name = 'New York Stock Exchange'
  group by c.name
) 
where rnk <= 5  
order by shares desc;  

SQL Get Other Rows From Aggregate Function

You will want to use a subquery that will get the max(b) by each A and then join that value back to your table to return the remaining columns that match the values of the subquery:

select *
from mytable t1
inner join
(
  select A, max(b) B
  from mytable
  where b >50
  group by a
) t2
  on t1.a = t2.a
  and t1.b = t2.b
where t1.b >50

See SQL Fiddle with Demo

How to write an SQL aggregate function/query

Use ORDER BY with LIMIT :

SELECT CAST(date AS DATE), SUM(amount) AS total_amount 
FROM table
WHERE date BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 00:00:00'
GROUP BY CAST(date AS DATE)
ORDER BY total_amount DESC
LIMIT 1;

If you are working with SQL Server then you can use TOP :

SELECT TOP (1) CAST(date AS DATE), SUM(amount) AS total_amount 
FROM table
WHERE date BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 00:00:00'
GROUP BY CAST(date AS DATE)
ORDER BY total_amount DESC;

If you want ties then you can use window function :

SELECT t.*
FROM (SELECT CAST(date AS DATE), SUM(amount) AS total_amount,
             RANK() OVER (ORDER BY SUM(amount) DESC) as Seq
      FROM table
      WHERE date BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 00:00:00'
      GROUP BY CAST(date AS DATE)
     ) t
WHERE seq = 1;

You can use CTE :

WITH CTE AS (
     SELECT CAST(date AS DATE), SUM(amount) AS total_amount 
     FROM table
     WHERE date BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 00:00:00'
     GROUP BY CAST(date AS DATE)
)  
SELECT c.*
FROM CTE C
WHERE C.total_amount = (SELECT MAX(total_amount) FROM CTE);

Note : If your DBMS doesn't support CTE expression then you need repeat the SELECT statement in Subquery.

SELECT CAST(date AS DATE), SUM(amount) AS total_amount 
FROM table
WHERE date BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 00:00:00'
GROUP BY CAST(date AS DATE)
HAVING SUM(amount) = (SELECT MAX(total_amount)
                      FROM (SELECT CAST(date AS DATE), SUM(amount) AS total_amount 
                            FROM table
                            WHERE date BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 00:00:00'
                            GROUP BY CAST(date AS DATE)
                           ) t
                     );

How do you select portions of a query in aggregate functions by some attribute

Seems like you're looking for the WITHIN GROUP functionality of STRING_AGG (Transact-SQL), e.g.:

/*
 * Data setup...
 */
create table dbo.Account (
  AccountID int
);
insert dbo.Account (AccountID) values
  (10),
  (11);

create table dbo.Tag (
  AccountID int,
  TagName varchar(20),
  TagType varchar(20)
);
insert dbo.Tag (AccountID, TagName, TagType) values
  (10, 'Red', 'color'),
  (10, 'Blue', 'color'),
  (10, 'Round', 'shape'),
  (11, 'Green', 'color'),
  (11, 'Rectangular', 'shape'),
  (11, 'Small', 'size'),
  (11, 'Medium', 'size');

/*
 * Example query...
 */
SELECT a.AccountID, 
       STRING_AGG(case when t.TagType = 'color' then t.TagName end, ', ') WITHIN GROUP (order by t.TagName) AS Colors,
       STRING_AGG(case when t.TagType = 'shape' then t.TagName end, ', ') WITHIN GROUP (order by t.TagName) AS Shapes,
       STRING_AGG(case when t.TagType = 'size' then t.TagName end, ', ') WITHIN GROUP (order by t.TagName) AS Sizes
FROM dbo.account a
INNER JOIN dbo.tag t ON a.AccountID = t.AccountID
GROUP BY a.AccountID

Which yields the result...

declare @t table (Column1 varchar(10), Column2 varchar(10), Column3 varchar(10), Column4 int)
insert into @t values 
('Col1Data0','Test1','Test2',1),
('Col1Data0','Test1','Test2',3),
('Col1Data1','Test1','Test2',2),
('Col1Data1','Test1','Test2',5)

SELECT Column1, Column2, Column3, 
    SUM(Column4) OVER (PARTITION BY Column1) AS SumColumn4
FROM @t

Column1     Column2 Column3 SumColumn4
Col1Data0   Test1   Test2   4
Col1Data0   Test1   Test2   4
Col1Data1   Test1   Test2   7
Col1Data1   Test1   Test2   7