CODEWITHSUNDEEP
sqloracle-database
Da Rek
Da Rek

Reputation: 3

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

Upvotes: 0

Views: 2396

Answers (2)

Da Rek
Da Rek

Reputation: 3

I have next question related to above query. How can I create one table with two SUM(Bal) columns with different Date for each other:

Acc --- Nam --- Bal(Dat1) -- Bal(Dat2)
101 --- One ------- 280 ---------- 300
102 --- Two ------- 340 ---------- 180

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

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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;

Upvotes: 2

Related Questions