max(sum(field query in Hive/SQL

Question

I have a table with lots of transactions for users across a month.

I need to take the hour from each day where Sum(cost) is at its highest.

I've tried MAX(SUM(Cost)) but get an error.

How would I go about doing this please?

here is some sample data

+-------------+------+----------+------+
|   user id   | hour |   date   | Cost |
+-------------+------+----------+------+
|      343252 |   13 | 20170101 | 21.5 |
|    32532532 |   13 | 20170101 | 22.5 |
|    35325325 |   13 | 20170101 | 30.5 |
|   325325325 |   13 | 20170101 | 10   |
|    64643643 |   12 | 20170101 | 22   |
|   643643643 |   12 | 20170101 | 31   |
|   436325234 |   13 | 20170101 | 15   |
|   213213213 |   13 | 20170101 | 12   |
| 53265436436 |   17 | 20170101 | 19   |
+-------------+------+----------+------+

Expected Output:

I need just one row per day, where it shows the total cost from the 'most expensive' hour. In this case, 13:00 had a total cost of 111.5

(1) Better, but please add it as text so it could be copied. (2) What do you want to do in a case of a tie between hours? — David דודו Markovitz
I've copied and pasted as text - but I didn't see a way to add it as a table, so it's not very readable - if you know how to make it a table that'll be great. In terms of ties between hours, that is extremely unlikely to happen. However, if it did, I am not too worried which one get's printed — user5832647

David דודו Markovitz David דודו Markovitz · Accepted Answer · 2017-06-02T08:06:07

select      hr
           ,dt
           ,total_cost

from       (select      dt
                       ,hr
                       ,sum(cost)   as total_cost
                       ,row_number () over
                        (
                            partition by    dt
                            order by        sum(cost) desc
                        ) as rn

            from        mytable
            group by    dt,hr
            ) t

where       rn = 1

+----+------------+------------+
| hr |     dt     | total_cost |
+----+------------+------------+
| 13 | 2017-01-01 | 111.5      |
+----+------------+------------+

max(sum(field query in Hive/SQL

2 Answers