Da Rek Da Rek - 4 months ago 10
SQL Question

ORACLE SQL Select only MAX of each group by number

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