CODEWITHSUNDEEP
sqloracle-databasewindow-functions
John
John

Reputation: 299

Max a Sum of a partition by

I have the below table.

I would like to SUM hours and partition by id then take the Max hours per custid. Below is what I have so far.

TableA

id    custid    projid   hours
 1      1010     Yellow    1
 1      1011     Yellow    2
 1      1012     Yellow    5
 1      1010     Yellow    5

SQL:

select SUM(HOURS)OVER (PARTITION BY ID ORDER BY cust) AS TOTAL_HRS
from tablea

Expected Output: The above SQL does not capture MAX hours

id    custid  projid  hours
 1      1010   Yellow  6

Upvotes: 0

Views: 2668

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I'm wondering if this does what you really want:

select cust, sum(hours) as total_hours
from tablea
order by sum(hours)
fetch first 1 row only;

It is unclear why you need the other values in the row. If you do, you could aggregate them into a single row.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

Using SUM as an analytic function usually means that you want to find a sum while retaining all your original records. But your expected output seems to imply an aggregation. So, I suggest using GROUP BY, and then querying that to find the row with the highest hours.

WITH cte AS (
    SELECT id, custid, projid, SUM(hours) AS hours
    FROM yourTable
    GROUP BY id, custid, projid
)

SELECT *
FROM cte
ORDER BY hours DESC
WHERE rownum = 1

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You want one more partition :

select *, sum(hours) over (partition by id, custid, projid order by cust) AS TOTAL_HRS
from tablea t
order by sum(hours) over (partition by id, custid, projid order by cust) desc
fetch first 1 row only;

Upvotes: 2

Related Questions