How to write an SQL aggregate function/query

Question

I have a query that displays the total value (sum of amount) for each day. The query:

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)

The CAST is to abbreviate the datetime format to just a date.

Now I want to select only the day which has the highest sum with the max function. To do this I tried writing the following aggregate query:

SELECT s.date, s.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)) s
WHERE s.total_amount = (SELECT MAX(s.total_amount) FROM table)

This does not work. I know the problem is with the final WHERE clause, but I need help with making it work.

I'm not using a specific SQL engine/DBMS so I need a general (universal) SQL query. — ire
– ire, Commented May 12, 2020 at 8:43
@ire: the "universal" SQL is the either the one row_number() (see zealous' answer) or the CTE from Yogesh's answer — user330315
– user330315, Commented May 12, 2020 at 9:29

Yogesh Sharma · Accepted Answer · 2020-05-12 08:59:29Z

2

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
                     );

edited May 12, 2020 at 8:59

answered May 12, 2020 at 8:34

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ire Over a year ago

This works but I'd like to know how to do it with an aggregate function. Do you know how I could make it work?

user330315 Over a year ago

@ire: you can't have an aggregate without using aggregate function. That doesn't make sense.

zealous · Accepted Answer · 2020-05-12 09:26:05Z

0

If you are using 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

Use window function row_number() - should work with MySQL 8.0, PostgreSQL, Oracle and SQL Server.

select
    date,
    total_amount
from
(
    SELECT 
        CAST(date AS DATE) as date, 
        SUM(amount) AS total_amount,
        row_number() over (order by SUM(amount) desc) as rnk 
    FROM table
    WHERE date BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 00:00:00'
    GROUP BY CAST(date AS DATE)
) val
where rnk = 1

edited May 12, 2020 at 9:26

user330315

answered May 12, 2020 at 8:35

zealous

7,5434 gold badges20 silver badges39 bronze badges

1 Comment

ire Over a year ago

I'm trying to avoid using the top or rnk and use an aggregate function like I showed in my post. Do you know how I could make it work?

VTi · Accepted Answer · 2020-05-12 09:35:48Z

0

SELECT s.dt, s.total_amount FROM (SELECT CAST(date AS DATE) as dt, SUM(amount) AS total_amount FROM table WHERE CAST(date as date) BETWEEN '2019-01-01' AND '2019-12-31' GROUP BY CAST(date AS DATE)) s WHERE s.total_amount = (Select max(total_amount) FROM (SELECT CAST(date AS DATE) as dt, SUM(amount) AS total_amount FROM table WHERE CAST(date as date) BETWEEN '2019-01-01' AND '2019-12-31' GROUP BY CAST(date AS DATE)) ss )

edited May 12, 2020 at 9:35

answered May 12, 2020 at 9:22

VTi

1,3298 silver badges14 bronze badges

1 Comment

VTi Over a year ago

I think the above will give you what you want but I am still trying to ascertain why you can not use rank or top functions for your need.

Collectives™ on Stack Overflow

How to write an SQL aggregate function/query

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related