Da Rek - 3 months ago 8x

SQL Question

I have problem with selecting sum of balances for specific Acc, Nam for a given period.

`SELECT Acc, Nam, SUM(Bal) FROM table WHERE`

MAX(Par) = (SELECT Par WHERE Acc IN (101,102) AND Dat >1105 AND Dat <1405)

AND Acc IN (101,102) AND Dat >1105 AND Dat <1405

GROUP BY Acc, Nam

Acc --- Nam --- Bal --- Par --- Dat

101 --- One --- 150 --- 131 --- 1205

101 --- One --- 120 --- 132 --- 1205

101 --- One --- 160 --- 133 --- 1305

102 --- Two --- 190 --- 121 --- 1205

102 --- Two --- 110 --- 122 --- 1305

102 --- Two --- 150 --- 123 --- 1305

The final output should be:

`Acc --- Nam --- Bal`

101 --- One --- 280

102 --- Two --- 340

So it will sum of all balances within the period separated by Account and Account Name. Only balances with the highest Par number per day should sum in total. Please advise.

UPDATE

I did it with below row_number method but what i received is just the total of all rows (without some of them). Below is the code which is showing: two accounts, two account descriptions, parameters, dates and balance. I would like to see two lines which will be the sum of all dates in a range for given account with the highest parameter number in a separate day. Final output shoul be only three columns: account, name and balance. This query should be able to chose max parameter of each day and sum all of these max values to one account.

`SELECT h.account, h.name, h.paramet, h.date, h.balance`

FROM h

WHERE TRUNC(h.date) > '01-Jun-16' AND TRUNC(h.date) < '10-Jun-16'

AND h.account IN ('410212','410213')

AND h.fund LIKE 'SSPSP

Answer

You can use `row_number()`

for this:

```
SELECT Acc, Nam, SUM(Bal)
FROM (SELECT t.*,
ROW_NUMBER() OVER (PARTITION BY Acc, Nam, Dat ORDER BY Par DESC) as seqnum
FROM table t
WHERE Acc IN (101,102) AND Dat > 1105 AND Dat < 1405
) t
WHERE seqnum = 1
GROUP BY Acc, Nam;
```

Source (Stackoverflow)

Comments