CODEWITHSUNDEEP
sqlhivehiveql
user5832647
user5832647

Reputation:

max(sum(field query in Hive/SQL

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

enter image description here

Upvotes: 1

Views: 756

Answers (2)

mitul
mitul

Reputation: 1

Try this:

select AVG(hour) as 'Hour',date as 'Date',sum(cost) as 'TotalCost' from dbo.Table_3 group by date

Upvotes: -2

David דודו Markovitz
David דודו Markovitz

Reputation: 44941

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      |
+----+------------+------------+

Upvotes: 1

Related Questions